package com.A21MobileSales.DataBase;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;

import org.json.JSONArray;
import org.json.JSONObject;

import com.A21.Object.A21OPPriceBook;
import com.A21.Object.A21OPPriceBookDetail;
import com.A21.Object.SpinnerObject;
import com.A21.Object.FrequentPurchaseList;
import com.A21.Object.Global;
import com.A21.Object.MSTMultiTierDiscountOP;
import com.A21.Object.MSTPriceBracket;
import com.A21.Object.Menu;
import com.A21.Object.SMUnitConversion;
import com.A21.Object.SUProductBrand;
import com.A21.Object.SUProductMade;
import com.A21.Object.SUProductModel;
import com.A21.Object.TrnDetail;
import com.A21.Object.A21OrderHeader;
import com.A21.Object.MSTCompany;
import com.A21.Object.MSTCompanyHolder;
import com.A21.Object.MSTCustomer;
import com.A21.Object.MSTCustomerHolder;
import com.A21.Object.MSTCustomerLocation;
import com.A21.Object.MSTGroupPriceTable;
import com.A21.Object.MSTPriceTable;
import com.A21.Object.MSTProduct;
import com.A21.Object.MSTProductHolder;
import com.A21.Object.MSTSettings;
import com.A21.Object.MSTUser;
import com.A21.Object.MSTUserHolder;
import com.A21.Object.SMGSTType;
import com.A21.Object.SMUnitConversionProd;
import com.A21.Object.SMUnitofMeasure;
import com.A21.Object.SUProductGroup;
import com.A21.Object.SUProductGroupHolder;
import com.A21.Object.UploadResult; 
import com.google.gson.Gson;
import com.google.gson.annotations.SerializedName;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DatabaseHelper extends SQLiteOpenHelper {

	// The Android's default system path of your application database.
	String DB_PATH = null;

	private static String DB_NAME = "A21MobileSales";
	private static final int DATABASE_VERSION = 1;
	private SQLiteDatabase myDataBase;

	private final Context myContext;

	/**
	 * Constructor Takes and keeps a reference of the passed context in order to
	 * access to the application assets and resources.
	 * 
	 * @param context
	 */
	public DatabaseHelper(Context context) {
		super(context, DB_NAME, null, DATABASE_VERSION);
		this.myContext = context;
		DB_PATH = "/data/data/" + context.getPackageName() + "/" + "databases/";
	}

	// GetMainMenu Details From MainMenuTbl
	public ArrayList<String> GetMainMenuDetails() {
		ArrayList<String> AryMainMenuDetails = new ArrayList<String>();
		try {
			openDataBase();

			Cursor c = myDataBase.rawQuery("Select * from Menu", null);
			while (c.moveToNext()) {
				AryMainMenuDetails.add(c.getString(0));
			}
			close();
		} catch (Exception ex) {
			ex.printStackTrace();
			close();
		}
		return AryMainMenuDetails;
	}

	public void DeleteTable(String _TableName) {
		openDataBase();
		myDataBase.delete(_TableName, null, null);
		close();
	}

	private String CursorToString(Cursor cursor) throws Exception {
		String json = "";
		try {
			JSONArray resultSet = new JSONArray();
			JSONObject returnObj = new JSONObject();
			cursor.moveToFirst();
			while (cursor.isAfterLast() == false) {
				int totalColumn = cursor.getColumnCount();
				JSONObject rowObject = new JSONObject();
				for (int i = 0; i < totalColumn; i++) {
					if (cursor.getColumnName(i) != null) {
						if (cursor.getString(i) != null) {
							rowObject.put(cursor.getColumnName(i),
									cursor.getString(i));
						} else {
							rowObject.put(cursor.getColumnName(i), "");
						}
					}

				}
				resultSet.put(rowObject);
				cursor.moveToNext();
			}
			cursor.close();
			json = resultSet.toString();
		} catch (Exception ex) {
			cursor.close();
			throw ex;
		}
		return json;
	}

	public void InsertMenu(ArrayList<Menu> rows) throws Exception {
		try {
			DeleteTable("Menu");
			if (rows != null && rows.size() > 0) {
				openDataBase();
				for (Menu item : rows) {
					ContentValues valInsert = new ContentValues();
					valInsert.put("Name", item.Name);
					myDataBase.insert("Menu", null, valInsert);
				}
				close();
			}
		} catch (Exception ex) {
			close();
			throw ex;
		}
	}

	
	public void InsertBrand(
			ArrayList<SUProductBrand> items) throws Exception {
		try {
			DeleteTable("SUProductBrand");
			if (items != null && items.size() > 0) {
				openDataBase();
				for (SUProductBrand item : items) {
					ContentValues valInsert = new ContentValues(); 
					valInsert.put("Code", item.Code);
					valInsert.put("Description", item.Description); 
					myDataBase.insert("SUProductBrand", null, valInsert);
				}

				close();
			}
		} catch (Exception ex) {
			close();
			throw ex;
		}
	}
	
	
	public void InsertMade(
			ArrayList<SUProductMade> items) throws Exception {
		try {
			DeleteTable("SUProductMade");
			if (items != null && items.size() > 0) {
				openDataBase();
				for (SUProductMade item : items) {
					ContentValues valInsert = new ContentValues(); 
					valInsert.put("Code", item.Code);
					valInsert.put("Description", item.Description); 
					myDataBase.insert("SUProductMade", null, valInsert);
				}

				close();
			}
		} catch (Exception ex) {
			close();
			throw ex;
		}
	}
	
	public void InsertModel(
			ArrayList<SUProductModel> items) throws Exception {
		try {
			DeleteTable("SUProductModel");
			if (items != null && items.size() > 0) {
				openDataBase();
				for (SUProductModel item : items) {
					ContentValues valInsert = new ContentValues(); 
					valInsert.put("Code", item.Code);
					valInsert.put("Description", item.Description); 
					myDataBase.insert("SUProductModel", null, valInsert);
				}

				close();
			}
		} catch (Exception ex) {
			close();
			throw ex;
		}
	}
	
	
	public void InsertMSTMultiTierDiscountOP(
			ArrayList<MSTMultiTierDiscountOP> items) throws Exception {
		try {
			DeleteTable("MSTMultiTierDiscountOP");
			if (items != null && items.size() > 0) {
				openDataBase();
				for (MSTMultiTierDiscountOP item : items) {
					ContentValues valInsert = new ContentValues();
					valInsert.put("ID", item.ID);
					valInsert.put("Code", item.Code);
					valInsert.put("Product", item.Product);
					valInsert.put("Currency", item.Currency);
					valInsert.put("UOM", item.UOM);
					valInsert.put("L1NormalPrice", item.L1NormalPrice);
					valInsert.put("L1TDiscAmt", item.L1TDiscAmt);
					valInsert.put("L1TDiscPercent", item.L1TDiscPercent);
					valInsert.put("L1DateFrom", item.L1DateFrom);
					valInsert.put("L1DateTo", item.L1DateTo);
					valInsert.put("L2DiscAmt", item.L2DiscAmt);
					valInsert.put("L2DiscPercent", item.L2DiscPercent);
					valInsert.put("L2TDiscAmt", item.L2TDiscAmt);
					valInsert.put("L2TDiscPercent", item.L2TDiscPercent);
					valInsert.put("L2DateFrom", item.L2DateFrom);
					valInsert.put("L2DateTo", item.L2DateTo);
					valInsert.put("L3MarkDownPrice", item.L3MarkDownPrice);
					valInsert.put("L3TDiscAmt", item.L3TDiscAmt);
					valInsert.put("L3TDiscPercent", item.L3TDiscPercent);
					valInsert.put("L3DateFrom", item.L3DateFrom);
					valInsert.put("L3DateTo", item.L3DateTo);
					myDataBase
							.insert("MSTMultiTierDiscountOP", null, valInsert);
				}

				close();
			}
		} catch (Exception ex) {
			close();
			throw ex;
		}
	}

	public MSTMultiTierDiscountOP GetCustomerMultiTierProduct(String Customer,
			String Product, String UOM, String Currency) throws Exception {
		MSTMultiTierDiscountOP item = null;
		try {
			openDataBase();
			Cursor c = myDataBase
					.rawQuery(
							"Select * from MSTMultiTierDiscountOP where Code=? and Product=? and  UOM=? and Currency=? ",
							new String[] { Customer, Product, UOM, Currency });
			while (c.moveToNext()) {
				item = new MSTMultiTierDiscountOP();
				item.ID = c.getInt(c.getColumnIndex("ID"));
				item.Code = c.getString(c.getColumnIndex("Code"));
				item.Product = c.getString(c.getColumnIndex("Product"));
				item.Currency = c.getString(c.getColumnIndex("Currency"));
				item.UOM = c.getString(c.getColumnIndex("UOM"));
				item.L1NormalPrice = c.getDouble(c
						.getColumnIndex("L1NormalPrice"));
				item.L1TDiscAmt = c.getDouble(c.getColumnIndex("L1TDiscAmt"));
				item.L1TDiscPercent = c.getDouble(c
						.getColumnIndex("L1TDiscPercent"));
				item.L2DiscAmt = c.getDouble(c.getColumnIndex("L2DiscAmt"));
				item.L2DiscPercent = c.getDouble(c
						.getColumnIndex("L2DiscPercent"));
				item.L2TDiscAmt = c.getDouble(c.getColumnIndex("L2TDiscAmt"));
				item.L2TDiscPercent = c.getDouble(c
						.getColumnIndex("L2TDiscPercent"));
				item.L3MarkDownPrice = c.getDouble(c
						.getColumnIndex("L3MarkDownPrice"));
				item.L3TDiscAmt = c.getDouble(c.getColumnIndex("L3TDiscAmt"));
				item.L3TDiscPercent = c.getDouble(c
						.getColumnIndex("L3TDiscPercent"));
				item.L1DateFrom = c.getString(c.getColumnIndex("L1DateFrom"));
				item.L1DateTo = c.getString(c.getColumnIndex("L1DateTo"));
				item.L2DateFrom = c.getString(c.getColumnIndex("L2DateFrom"));
				item.L2DateTo = c.getString(c.getColumnIndex("L2DateTo"));
				item.L3DateFrom = c.getString(c.getColumnIndex("L3DateFrom"));
				item.L3DateTo = c.getString(c.getColumnIndex("L3DateTo"));
			}
			close();
		} catch (Exception ex) {
			close();
			throw ex;
		}
		return item;
	}

	public ArrayList<MSTProduct> GetAllMultiTierProductDisplay(String Customer,
			String Currency) throws Exception {
		ArrayList<MSTProduct> items = new ArrayList<MSTProduct>();
		try {
			openDataBase();
			Cursor c = myDataBase
					.rawQuery(
							"Select A.Product,A.PName,A.Brand,A.Model,A.Made from MSTProduct A inner join MSTMultiTierDiscountOP B on A.Product=B.Product and A.SalesUnit=B.UOM Where B.Code=? and B.Currency=?",
							new String[] { Customer, Currency });
			while (c.moveToNext()) {
				MSTProduct item = new MSTProduct();
				item.Product = c.getString(c.getColumnIndex("Product"));
				item.PName = c.getString(c.getColumnIndex("PName"));
				item.Brand = c.getString(c.getColumnIndex("Brand"));
				item.Made = c.getString(c.getColumnIndex("Made"));
				item.Model = c.getString(c.getColumnIndex("Model"));
				items.add(item);
			}
			close();
		} catch (Exception ex) {
			close();
			throw ex;
		}
		return items;
	}

	public void InsertMSTUers(ArrayList<MSTUser> users) throws Exception {
		try {
			DeleteTable("MSTUser");
			if (users != null && users.size() > 0) {
				openDataBase();
				for (MSTUser item : users) {
					ContentValues valInsert = new ContentValues();
					valInsert.put("User", item.User);
					valInsert.put("UName", item.UName);
					valInsert.put("NotActive", item.NotActive);
					valInsert.put("Password", item.Password);
					valInsert.put("Employee", item.Employee);
					valInsert.put("EmployeeName", item.EmployeeName); 
					valInsert.put("ReservedField1", item.DocPrefix);
					valInsert.put("ReservedField2", item.ReservedField2);
					valInsert.put("ReservedField3", item.ReservedField3);
					valInsert.put("ReservedField4", item.ReservedField4);
					valInsert.put("ReservedField5", item.ReservedField5);
					myDataBase.insert("MSTUser", null, valInsert);
				}

				close();
			}
		} catch (Exception ex) {
			close();
			throw ex;
		}
	}

	public MSTUser GetUserByUserId(String _UserId) throws Exception {
		MSTUser usr = null;
		try {
			openDataBase();
			Cursor c = myDataBase.rawQuery(
					"Select * from MSTUser where LOWER(User) =LOWER(?)",
					new String[] { _UserId });

			while (c.moveToNext()) {
				usr = new MSTUser();
				usr.User = c.getString(c.getColumnIndex("User"));
				usr.UName = c.getString(c.getColumnIndex("UName"));
				usr.NotActive = Boolean.valueOf(c.getString(c
						.getColumnIndex("NotActive")));
				usr.Password = Integer.valueOf(c.getString(c
						.getColumnIndex("Password")));
				usr.Employee = c.getString(c.getColumnIndex("Employee")); 
				usr.EmployeeName = c.getString(c.getColumnIndex("EmployeeName"));
				usr.DocPrefix = c.getString(c.getColumnIndex("ReservedField1"));
				usr.ReservedField1 = c.getString(c
						.getColumnIndex("ReservedField1"));
				usr.ReservedField2 = c.getString(c
						.getColumnIndex("ReservedField2"));
				usr.ReservedField3 = c.getString(c
						.getColumnIndex("ReservedField3"));
				usr.ReservedField4 = c.getString(c
						.getColumnIndex("ReservedField4"));
				usr.ReservedField5 = c.getString(c
						.getColumnIndex("ReservedField5"));
			}

			/*
			 * String JsonString = CursorToString(c); if (!JsonString.equals("")
			 * && !JsonString.equals("[]")) { JsonString = "{\"MSTUserfields\":"
			 * + JsonString + "}"; MSTUserHolder data = new
			 * Gson().fromJson(JsonString, MSTUserHolder.class);
			 * ArrayList<MSTUser> rows = new ArrayList<MSTUser>(
			 * Arrays.asList(data.MSTUserfields)); usr = rows.get(0); }
			 */
			close();

		} catch (Exception ex) {
			close();
			throw ex;
		}

		return usr;
	}

	public MSTUser CheckUserNameAndPassWord(String UserName, String PassWord)
			throws Exception {
		MSTUser user = null;
		try {
			openDataBase();
			Cursor c = myDataBase
					.rawQuery(
							"Select * from MSTUser where LOWER(User) =LOWER(?) and password =?",
							new String[] { UserName, PassWord });
			while (c.moveToNext()) {
				user = GetUserByUserId(UserName);
			}
		} catch (Exception ex) {
			close();
			throw ex;
		}
		return user;
	}

	public void InsertCompany(ArrayList<MSTCompany> rows) throws Exception {
		try {
			DeleteTable("MSTCompany");
			if (rows != null && rows.size() > 0) {
				openDataBase();
				for (MSTCompany item : rows) {
					ContentValues valInsert = new ContentValues();
					valInsert.put("Code", item.Code);
					valInsert.put("CName", item.CName);
					valInsert.put("Add1", item.Add1);
					valInsert.put("Add2", item.Add2);
					valInsert.put("Add3", item.Add3);
					valInsert.put("Add4", item.Add4);
					valInsert.put("Pin", item.Pin);
					valInsert.put("RegNo", item.RegNo);
					valInsert.put("ReservedField1", item.ReservedField1);
					valInsert.put("ReservedField2", item.ReservedField2);
					valInsert.put("ReservedField3", item.ReservedField3);
					valInsert.put("ReservedField4", item.ReservedField4);
					myDataBase.insert("MSTCompany", null, valInsert);
				}

				close();
			}
		} catch (Exception ex) {
			close();
			throw ex;
		}
	}

	public MSTCompany GetCompanyByCode(String _Code) throws Exception {
		MSTCompany item = null;
		try {
			openDataBase();
			Cursor c1 = myDataBase.rawQuery("Select * from MSTCompany", null);
			while (c1.moveToNext()) {
				item.Code = c1.getString(c1.getColumnIndex("Code"));
			}
			Cursor c = myDataBase.rawQuery(
					"Select * from MSTCompany where Code=? ",
					new String[] { _Code });

			while (c.moveToNext()) {
				item = new MSTCompany();
				item.Code = c.getString(c.getColumnIndex("Code"));
				item.CName = c.getString(c.getColumnIndex("CName"));
				item.Add1 = c.getString(c.getColumnIndex("Add1"));
				item.Add2 = c.getString(c.getColumnIndex("Add2"));
				item.Add3 = c.getString(c.getColumnIndex("Add3"));
				item.Add4 = c.getString(c.getColumnIndex("Add4"));
				item.Pin = c.getString(c.getColumnIndex("Pin"));
				item.RegNo = c.getString(c.getColumnIndex("RegNo"));
				item.ReservedField1 = c.getString(c
						.getColumnIndex("ReservedField1"));
				item.ReservedField2 = c.getString(c
						.getColumnIndex("ReservedField2"));
				item.ReservedField3 = c.getString(c
						.getColumnIndex("ReservedField3"));
				item.ReservedField4 = c.getString(c
						.getColumnIndex("ReservedField4"));
			}

			/*
			 * String JsonString = CursorToString(c); if (!JsonString.equals("")
			 * && !JsonString.equals("[]")) { JsonString =
			 * "{\"MSTCompanyfields\":" + JsonString + "}"; MSTCompanyHolder
			 * data = new Gson().fromJson(JsonString, MSTCompanyHolder.class);
			 * ArrayList<MSTCompany> rows = new ArrayList<MSTCompany>(
			 * Arrays.asList(data.MSTCompanyfields)); item = rows.get(0); }
			 */
			close();

		} catch (Exception ex) {
			close();
			throw ex;
		}

		return item;
	}

	public void InsertCustomers(ArrayList<MSTCustomer> rows) throws Exception {
		try {
			DeleteTable("MSTCustomer");
			if (rows != null && rows.size() > 0) {
				openDataBase();
				for (MSTCustomer item : rows) {
					ContentValues valInsert = new ContentValues();
					valInsert.put("CodeID", item.CodeID);
					valInsert.put("Code", item.Code);
					valInsert.put("CName", item.CName);
					valInsert.put("Type", item.Type);
					valInsert.put("Add1", item.Add1);
					valInsert.put("Add2", item.Add2);
					valInsert.put("Add3", item.Add3);
					valInsert.put("Add4", item.Add4);
					valInsert.put("Pin", item.Pin);
					valInsert.put("Contact", item.Contact);
					valInsert.put("Telephone1", item.Telephone1);
					valInsert.put("Telephone2", item.Telephone2);
					valInsert.put("Mobile", item.Mobile);
					valInsert.put("Fax", item.Fax);
					valInsert.put("Email", item.Email);
					valInsert.put("Country", item.Country);
					valInsert.put("SalesPerson", item.SalesPerson);
					valInsert.put("GSTType", item.GSTType);
					valInsert.put("Remarks", item.Remarks);
					valInsert.put("CusType", item.CusType);
					valInsert.put("CusAcctGrp", item.CusAcctGrp);
					valInsert.put("CusClass1", item.CusClass1);
					valInsert.put("CusClass2", item.CusClass2);
					valInsert.put("CusClass3", item.CusClass3);
					valInsert.put("CusPayTerm", item.CusPayTerm);
					valInsert.put("CusPayMode", item.CusPayMode);
					valInsert.put("CusDelTerm", item.CusDelTerm);
					valInsert.put("CusDelMode", item.CusDelMode);
					valInsert.put("CusCreditPeriod", item.CusCreditPeriod);
					valInsert.put("CusCreditUnit", item.CusCreditUnit);
					valInsert.put("CusCreditLimit", item.CusCreditLimit);
					valInsert.put("CusCreditUsed", item.CusCreditUsed);
					valInsert.put("CusFlatDisc", item.CusFlatDisc);
					valInsert.put("DiscountGroup", item.DiscountGroup);
					valInsert.put("Territory", item.Territory);
					valInsert.put("GSTInc", item.GSTInc);
					valInsert.put("Currency", item.Currency);
					valInsert.put("Bucket0", item.Bucket0);
					valInsert.put("Bucket1", item.Bucket1);
					valInsert.put("Bucket2", item.Bucket2);
					valInsert.put("Bucket3", item.Bucket3);
					valInsert.put("Bucket4", item.Bucket4);
					valInsert.put("Bucket5", item.Bucket5);
					valInsert.put("BucketBalance", item.BucketBalance);
					valInsert.put("PriceBookID", item.PriceBookID);
					valInsert.put("ReservedField1", item.ReservedField1);
					valInsert.put("ReservedField2", item.ReservedField2);
					valInsert.put("ReservedField3", item.ReservedField3);
					valInsert.put("ReservedField4", item.ReservedField4);
					valInsert.put("ReservedField5", item.ReservedField5);
					myDataBase.insert("MSTCustomer", null, valInsert);
				}

				close();
			}
		} catch (Exception ex) {
			close();
			throw ex;
		}
	}

	public MSTCustomer GetCustomerByCode(String _Code) throws Exception {
		MSTCustomer item = null;
		try {
			openDataBase();
			Cursor c = myDataBase.rawQuery(
					"Select * from MSTCustomer where Code=? ",
					new String[] { _Code });

			while (c.moveToNext()) {
				item = new MSTCustomer();
				item.CodeID = Integer.valueOf(c.getString(c
						.getColumnIndex("CodeID")));
				item.Code = c.getString(c.getColumnIndex("Code"));
				item.CName = c.getString(c.getColumnIndex("CName"));
				item.Type = c.getString(c.getColumnIndex("Type"));
				item.Add1 = c.getString(c.getColumnIndex("Add1"));
				item.Add2 = c.getString(c.getColumnIndex("Add2"));
				item.Add3 = c.getString(c.getColumnIndex("Add3"));
				item.Add4 = c.getString(c.getColumnIndex("Add4"));
				item.Pin = c.getString(c.getColumnIndex("Pin"));
				item.Contact = c.getString(c.getColumnIndex("Contact"));
				item.Telephone1 = c.getString(c.getColumnIndex("Telephone1"));
				item.Telephone2 = c.getString(c.getColumnIndex("Telephone2"));
				item.Mobile = c.getString(c.getColumnIndex("Mobile"));
				item.Fax = c.getString(c.getColumnIndex("Fax"));
				item.Email = c.getString(c.getColumnIndex("Email"));
				item.Country = c.getString(c.getColumnIndex("Country"));
				item.SalesPerson = c.getString(c.getColumnIndex("SalesPerson"));
				item.GSTType = c.getString(c.getColumnIndex("GSTType"));
				item.Remarks = c.getString(c.getColumnIndex("Remarks"));
				item.CusType = c.getString(c.getColumnIndex("CusType"));
				item.CusAcctGrp = c.getString(c.getColumnIndex("CusAcctGrp"));
				item.CusClass1 = c.getString(c.getColumnIndex("CusClass1"));
				item.CusClass2 = c.getString(c.getColumnIndex("CusClass2"));
				item.CusClass3 = c.getString(c.getColumnIndex("CusClass3"));
				item.CusPayTerm = c.getString(c.getColumnIndex("CusPayTerm"));
				item.CusPayMode = c.getString(c.getColumnIndex("CusPayMode"));
				item.CusDelTerm = c.getString(c.getColumnIndex("CusDelTerm"));
				item.CusDelMode = c.getString(c.getColumnIndex("CusDelMode"));
				item.PriceBookID = c.getInt(c.getColumnIndex("PriceBookID"));
				item.CusCreditPeriod = Integer.valueOf(c.getString(c
						.getColumnIndex("CusCreditPeriod")));
				item.CusCreditUnit = c.getString(c
						.getColumnIndex("CusCreditUnit"));
				item.CusCreditLimit = c.getDouble(c
						.getColumnIndex("CusCreditLimit"));
				item.CusCreditUsed = c.getDouble(c
						.getColumnIndex("CusCreditUsed"));
				item.CusFlatDisc = c.getDouble(c.getColumnIndex("CusFlatDisc"));
				item.DiscountGroup = c.getString(c
						.getColumnIndex("DiscountGroup"));
				item.Territory = c.getString(c.getColumnIndex("Territory"));
				item.GSTInc = c.getInt(c.getColumnIndex("GSTInc")) > 0;
				item.Currency = c.getString(c.getColumnIndex("Currency"));
				item.Bucket0 = c.getDouble((c.getColumnIndex("Bucket0")));
				item.Bucket1 = c.getDouble(c.getColumnIndex("Bucket1"));
				item.Bucket2 = c.getDouble(c.getColumnIndex("Bucket2"));
				item.Bucket3 = c.getDouble(c.getColumnIndex("Bucket3"));
				item.Bucket4 = c.getDouble(c.getColumnIndex("Bucket4"));
				item.Bucket5 = c.getDouble(c.getColumnIndex("Bucket5"));
				item.BucketBalance = c.getDouble(c
						.getColumnIndex("BucketBalance"));
				item.ReservedField1 = c.getString(c
						.getColumnIndex("ReservedField1"));
				item.ReservedField2 = c.getString(c
						.getColumnIndex("ReservedField2"));
				item.ReservedField3 = c.getString(c
						.getColumnIndex("ReservedField3"));
				item.ReservedField4 = c.getString(c
						.getColumnIndex("ReservedField4"));
				item.ReservedField5 = c.getString(c
						.getColumnIndex("ReservedField5"));
			}

			/*
			 * String JsonString = CursorToString(c); if (!JsonString.equals("")
			 * && !JsonString.equals("[]")) { JsonString =
			 * "{\"MSTCustomerfields\":" + JsonString + "}"; MSTCustomerHolder
			 * data = new Gson().fromJson(JsonString, MSTCustomerHolder.class);
			 * ArrayList<MSTCustomer> rows = new ArrayList<MSTCustomer>(
			 * Arrays.asList(data.MSTCustomerfields)); item = rows.get(0); }
			 */
			close();

		} catch (Exception ex) {
			close();
			throw ex;
		}

		return item;
	}

	public ArrayList<MSTCustomer> GetAllCustomers(boolean _short)
			throws Exception {
		ArrayList<MSTCustomer> lstCustomers = new ArrayList<MSTCustomer>();
		try {
			openDataBase();
			String cols = _short ? "Code,CName" : "*";
			Cursor c = myDataBase.rawQuery("Select " + cols
					+ " from MSTCustomer", null);
			while (c.moveToNext()) {
				MSTCustomer item = new MSTCustomer();
				item.Code = c.getString(c.getColumnIndex("Code"));
				item.CName = c.getString(c.getColumnIndex("CName"));
				if (!_short) {
					item.CodeID = Integer.valueOf(c.getString(c
							.getColumnIndex("CodeID")));
					item.Type = c.getString(c.getColumnIndex("Type"));
					item.Add1 = c.getString(c.getColumnIndex("Add1"));
					item.Add2 = c.getString(c.getColumnIndex("Add2"));
					item.Add3 = c.getString(c.getColumnIndex("Add3"));
					item.Add4 = c.getString(c.getColumnIndex("Add4"));
					item.Pin = c.getString(c.getColumnIndex("Pin"));
					item.Contact = c.getString(c.getColumnIndex("Contact"));
					item.Telephone1 = c.getString(c
							.getColumnIndex("Telephone1"));
					item.Telephone2 = c.getString(c
							.getColumnIndex("Telephone2"));
					item.Mobile = c.getString(c.getColumnIndex("Mobile"));
					item.Fax = c.getString(c.getColumnIndex("Fax"));
					item.Email = c.getString(c.getColumnIndex("Email"));
					item.Country = c.getString(c.getColumnIndex("Country"));
					item.SalesPerson = c.getString(c
							.getColumnIndex("SalesPerson"));
					item.GSTType = c.getString(c.getColumnIndex("GSTType"));
					item.Remarks = c.getString(c.getColumnIndex("Remarks"));
					item.CusType = c.getString(c.getColumnIndex("CusType"));
					item.CusAcctGrp = c.getString(c
							.getColumnIndex("CusAcctGrp"));
					item.CusClass1 = c.getString(c.getColumnIndex("CusClass1"));
					item.CusClass2 = c.getString(c.getColumnIndex("CusClass2"));
					item.CusClass3 = c.getString(c.getColumnIndex("CusClass3"));
					item.CusPayTerm = c.getString(c
							.getColumnIndex("CusPayTerm"));
					item.CusPayMode = c.getString(c
							.getColumnIndex("CusPayMode"));
					item.CusDelTerm = c.getString(c
							.getColumnIndex("CusDelTerm"));
					item.CusDelMode = c.getString(c
							.getColumnIndex("CusDelMode"));
					item.CusCreditPeriod = Integer.valueOf(c.getString(c
							.getColumnIndex("CusCreditPeriod")));
					item.CusCreditUnit = c.getString(c
							.getColumnIndex("CusCreditUnit"));
					item.CusCreditLimit = c.getDouble(c
							.getColumnIndex("CusCreditLimit"));
					item.CusCreditUsed = c.getDouble(c
							.getColumnIndex("CusCreditUsed"));
					item.CusFlatDisc = c.getDouble(c
							.getColumnIndex("CusFlatDisc"));
					item.DiscountGroup = c.getString(c
							.getColumnIndex("DiscountGroup"));
					item.Territory = c.getString(c.getColumnIndex("Territory"));
					item.GSTInc = c.getInt(c.getColumnIndex("GSTInc")) > 0;
					item.Bucket0 = c.getDouble((c.getColumnIndex("Bucket0")));
					item.Bucket1 = c.getDouble(c.getColumnIndex("Bucket1"));
					item.Bucket2 = c.getDouble(c.getColumnIndex("Bucket2"));
					item.Bucket3 = c.getDouble(c.getColumnIndex("Bucket3"));
					item.Bucket4 = c.getDouble(c.getColumnIndex("Bucket4"));
					item.Bucket5 = c.getDouble(c.getColumnIndex("Bucket5"));
					item.BucketBalance = c.getDouble(c
							.getColumnIndex("BucketBalance"));
					item.ReservedField1 = c.getString(c
							.getColumnIndex("ReservedField1"));
					item.ReservedField2 = c.getString(c
							.getColumnIndex("ReservedField2"));
					item.ReservedField3 = c.getString(c
							.getColumnIndex("ReservedField3"));
					item.ReservedField4 = c.getString(c
							.getColumnIndex("ReservedField4"));
					item.ReservedField5 = c.getString(c
							.getColumnIndex("ReservedField5"));

				}
				lstCustomers.add(item);
			}

			/*
			 * String JsonString = CursorToString(c); if (!JsonString.equals("")
			 * && !JsonString.equals("[]")) { JsonString =
			 * "{\"MSTCustomerfields\":" + JsonString + "}"; MSTCustomerHolder
			 * data = new Gson().fromJson(JsonString, MSTCustomerHolder.class);
			 * lstCustomers = new ArrayList<MSTCustomer>(
			 * Arrays.asList(data.MSTCustomerfields)); }
			 */
			close();
		} catch (Exception ex) {
			close();
			throw ex;
		}
		return lstCustomers;
	}

	public ArrayList<MSTCustomer> SearchCustomer(String _Search, boolean _short) {
		ArrayList<MSTCustomer> lstCustomers = new ArrayList<MSTCustomer>();
		try {
			openDataBase();
			String cols = _short ? "Code,CName" : "*";
			Cursor c = myDataBase.rawQuery("Select " + cols
					+ " from MSTCustomer where LOWER(Code) like '%" + _Search
					+ "%' or LOWER(CName) like '%" + _Search + "%'", null);
			while (c.moveToNext()) {
				MSTCustomer item = new MSTCustomer();

				item.Code = c.getString(c.getColumnIndex("Code"));
				item.CName = c.getString(c.getColumnIndex("CName"));
				if (!_short) {
					item.CodeID = Integer.valueOf(c.getString(c
							.getColumnIndex("CodeID")));
					item.Type = c.getString(c.getColumnIndex("Type"));
					item.Add1 = c.getString(c.getColumnIndex("Add1"));
					item.Add2 = c.getString(c.getColumnIndex("Add2"));
					item.Add3 = c.getString(c.getColumnIndex("Add3"));
					item.Add4 = c.getString(c.getColumnIndex("Add4"));
					item.Pin = c.getString(c.getColumnIndex("Pin"));
					item.Contact = c.getString(c.getColumnIndex("Contact"));
					item.Telephone1 = c.getString(c
							.getColumnIndex("Telephone1"));
					item.Telephone2 = c.getString(c
							.getColumnIndex("Telephone2"));
					item.Mobile = c.getString(c.getColumnIndex("Mobile"));
					item.Fax = c.getString(c.getColumnIndex("Fax"));
					item.Email = c.getString(c.getColumnIndex("Email"));
					item.Country = c.getString(c.getColumnIndex("Country"));
					item.SalesPerson = c.getString(c
							.getColumnIndex("SalesPerson"));
					item.GSTType = c.getString(c.getColumnIndex("GSTType"));
					item.Remarks = c.getString(c.getColumnIndex("Remarks"));
					item.CusType = c.getString(c.getColumnIndex("CusType"));
					item.CusAcctGrp = c.getString(c
							.getColumnIndex("CusAcctGrp"));
					item.CusClass1 = c.getString(c.getColumnIndex("CusClass1"));
					item.CusClass2 = c.getString(c.getColumnIndex("CusClass2"));
					item.CusClass3 = c.getString(c.getColumnIndex("CusClass3"));
					item.CusPayTerm = c.getString(c
							.getColumnIndex("CusPayTerm"));
					item.CusPayMode = c.getString(c
							.getColumnIndex("CusPayMode"));
					item.CusDelTerm = c.getString(c
							.getColumnIndex("CusDelTerm"));
					item.CusDelMode = c.getString(c
							.getColumnIndex("CusDelMode"));
					item.CusCreditPeriod = Integer.valueOf(c.getString(c
							.getColumnIndex("CusCreditPeriod")));
					item.CusCreditUnit = c.getString(c
							.getColumnIndex("CusCreditUnit"));
					item.CusCreditLimit = c.getDouble(c
							.getColumnIndex("CusCreditLimit"));
					item.CusCreditUsed = c.getDouble(c
							.getColumnIndex("CusCreditUsed"));
					item.CusFlatDisc = c.getDouble(c
							.getColumnIndex("CusFlatDisc"));
					item.DiscountGroup = c.getString(c
							.getColumnIndex("DiscountGroup"));
					item.Territory = c.getString(c.getColumnIndex("Territory"));
					item.GSTInc = c.getInt(c.getColumnIndex("GSTInc")) > 0;

					item.Bucket0 = c.getDouble((c.getColumnIndex("Bucket0")));
					item.Bucket1 = c.getDouble(c.getColumnIndex("Bucket1"));
					item.Bucket2 = c.getDouble(c.getColumnIndex("Bucket2"));
					item.Bucket3 = c.getDouble(c.getColumnIndex("Bucket3"));
					item.Bucket4 = c.getDouble(c.getColumnIndex("Bucket4"));
					item.Bucket5 = c.getDouble(c.getColumnIndex("Bucket5"));
					item.BucketBalance = c.getDouble(c
							.getColumnIndex("BucketBalance"));
					item.ReservedField1 = c.getString(c
							.getColumnIndex("ReservedField1"));
					item.ReservedField2 = c.getString(c
							.getColumnIndex("ReservedField2"));
					item.ReservedField3 = c.getString(c
							.getColumnIndex("ReservedField3"));
					item.ReservedField4 = c.getString(c
							.getColumnIndex("ReservedField4"));
					item.ReservedField5 = c.getString(c
							.getColumnIndex("ReservedField5"));
				}

				lstCustomers.add(item);
			}
			/*
			 * String JsonString = CursorToString(c); if (!JsonString.equals("")
			 * && !JsonString.equals("[]")) { JsonString =
			 * "{\"MSTCustomerfields\":" + JsonString + "}"; MSTCustomerHolder
			 * data = new Gson().fromJson(JsonString, MSTCustomerHolder.class);
			 * lstCustomers = new ArrayList<MSTCustomer>(
			 * Arrays.asList(data.MSTCustomerfields)); }
			 */
			close();

		} catch (Exception ex) {
			close();
			ex.printStackTrace();
		}
		return lstCustomers;
	}

	public void InsertCustomersLocations(ArrayList<MSTCustomerLocation> rows)
			throws Exception {
		try {

			DeleteTable("MSTCustomerLocation");
			if (rows != null && rows.size() > 0) {
				openDataBase();
				for (MSTCustomerLocation item : rows) {
					ContentValues valInsert = new ContentValues();
					valInsert.put("Code", item.Code);
					valInsert.put("Location", item.Location);
					valInsert.put("Add1", item.Add1);
					valInsert.put("Add2", item.Add2);
					valInsert.put("Add3", item.Add3);
					valInsert.put("Add4", item.Add4);
					valInsert.put("Add5", item.Add5);
					valInsert.put("Pin", item.Pin);
					valInsert.put("SalesPerson", item.SalesPerson);
					valInsert.put("DefaultLocation", item.DefaultLocation);
					valInsert.put("Description", item.Description);
					valInsert.put("DelMode", item.DelMode);
					valInsert.put("ReservedField1", item.ReservedField1);
					valInsert.put("ReservedField2", item.ReservedField2);
					valInsert.put("ReservedField3", item.ReservedField3);
					valInsert.put("ReservedField4", item.ReservedField4);
					valInsert.put("ReservedField5", item.ReservedField5);
					myDataBase.insert("MSTCustomerLocation", null, valInsert);
				}

				close();
			}
		} catch (Exception ex) {
			close();
			throw ex;
		}
	}

	public MSTCustomerLocation GetCustomerByCodeAndLocation(String _Code,
			String _Location) {
		MSTCustomerLocation item = null;
		try {
			openDataBase();
			Cursor c = myDataBase
					.rawQuery(
							"Select * from MSTCustomerLocation where Code=? and Location=? ",
							new String[] { _Code, _Location });
			while (c.moveToNext()) {
				item = new MSTCustomerLocation();
				item.Code = c.getString(c.getColumnIndex("Code"));
				item.Location = c.getString(c.getColumnIndex("Location"));
				item.Add1 = c.getString(c.getColumnIndex("Add1"));
				item.Add2 = c.getString(c.getColumnIndex("Add2"));
				item.Add3 = c.getString(c.getColumnIndex("Add3"));
				item.Add4 = c.getString(c.getColumnIndex("Add4"));
				item.Pin = c.getString(c.getColumnIndex("Pin"));
				item.SalesPerson = c.getString(c.getColumnIndex("SalesPerson"));
				item.DefaultLocation = Boolean.valueOf(c.getString(c
						.getColumnIndex("DefaultLocation")));
				item.Description = c.getString(c.getColumnIndex("Description"));
				item.DelMode = c.getString(c.getColumnIndex("DelMode"));
				item.ReservedField1 = c.getString(c
						.getColumnIndex("ReservedField1"));
				item.ReservedField2 = c.getString(c
						.getColumnIndex("ReservedField2"));
				item.ReservedField3 = c.getString(c
						.getColumnIndex("ReservedField3"));
				item.ReservedField4 = c.getString(c
						.getColumnIndex("ReservedField4"));
				item.ReservedField5 = c.getString(c
						.getColumnIndex("ReservedField5"));
			}

		} catch (Exception ex) {
			close();
			ex.printStackTrace();
		}

		return item;
	}

	public ArrayList<MSTCustomerLocation> GetAllCustomerByCode(String _Code) {
		ArrayList<MSTCustomerLocation> items = new ArrayList<MSTCustomerLocation>();
		try {
			openDataBase();
			Cursor c = myDataBase.rawQuery(
					"Select * from MSTCustomerLocation where Code=? ",
					new String[] { _Code });
			while (c.moveToNext()) {
				MSTCustomerLocation item = new MSTCustomerLocation();
				item.Code = c.getString(c.getColumnIndex("Code"));
				item.Location = c.getString(c.getColumnIndex("Location"));
				item.Add1 = c.getString(c.getColumnIndex("Add1"));
				item.Add2 = c.getString(c.getColumnIndex("Add2"));
				item.Add3 = c.getString(c.getColumnIndex("Add3"));
				item.Add4 = c.getString(c.getColumnIndex("Add4"));
				item.Pin = c.getString(c.getColumnIndex("Pin"));
				item.SalesPerson = c.getString(c.getColumnIndex("SalesPerson"));
				item.DefaultLocation = Boolean.valueOf(c.getString(c
						.getColumnIndex("DefaultLocation")));
				item.Description = c.getString(c.getColumnIndex("Description"));
				item.DelMode = c.getString(c.getColumnIndex("DelMode"));
				item.ReservedField1 = c.getString(c
						.getColumnIndex("ReservedField1"));
				item.ReservedField2 = c.getString(c
						.getColumnIndex("ReservedField2"));
				item.ReservedField3 = c.getString(c
						.getColumnIndex("ReservedField3"));
				item.ReservedField4 = c.getString(c
						.getColumnIndex("ReservedField4"));
				item.ReservedField5 = c.getString(c
						.getColumnIndex("ReservedField5"));
				items.add(item);
			}

		} catch (Exception ex) {
			close();
			ex.printStackTrace();
		}

		return items;
	}

	public void InsertGST(ArrayList<SMGSTType> rows) throws Exception {
		try {
			DeleteTable("SMGSTType");
			if (rows != null && rows.size() > 0) {
				openDataBase();
				for (SMGSTType item : rows) {
					ContentValues valInsert = new ContentValues();
					valInsert.put("Code", item.Code);
					valInsert.put("Description", item.Description);
					valInsert.put("GST", item.GST);
					valInsert.put("Active", item.Active);
					valInsert.put("InSales", item.InSales);
					valInsert.put("ReservedField1", item.ReservedField1);
					valInsert.put("ReservedField2", item.ReservedField2);
					valInsert.put("ReservedField3", item.ReservedField3);
					valInsert.put("ReservedField4", item.ReservedField4);
					valInsert.put("ReservedField5", item.ReservedField5);
					myDataBase.insert("SMGSTType", null, valInsert);
				}

				close();
			}
		} catch (Exception ex) {
			close();
			throw ex;
		}
	}

	public SMGSTType GetGSTByCode(String _Code) {
		SMGSTType item = null;
		try {
			openDataBase();
			Cursor c = myDataBase.rawQuery(
					"Select * from SMGSTType where Code=?",
					new String[] { _Code });
			while (c.moveToNext()) {
				item = new SMGSTType();
				item.Code = c.getString(c.getColumnIndex("Code"));
				item.Description = c.getString(c.getColumnIndex("Description"));
				item.GST = c.getDouble(c.getColumnIndex("GST"));
				item.Active = Boolean.valueOf(c.getString(c
						.getColumnIndex("Active")));
				item.InSales = Boolean.valueOf(c.getString(c
						.getColumnIndex("InSales")));
				item.ReservedField1 = c.getString(c
						.getColumnIndex("ReservedField1"));
				item.ReservedField2 = c.getString(c
						.getColumnIndex("ReservedField2"));
				item.ReservedField3 = c.getString(c
						.getColumnIndex("ReservedField3"));
				item.ReservedField4 = c.getString(c
						.getColumnIndex("ReservedField4"));
				item.ReservedField5 = c.getString(c
						.getColumnIndex("ReservedField5"));
			}

		} catch (Exception ex) {
			close();
			ex.printStackTrace();
		}

		return item;
	}

	public void InsertGroupPriceTables(ArrayList<MSTGroupPriceTable> rows)
			throws Exception {
		try {
			DeleteTable("MSTGroupPriceTable");
			if (rows != null && rows.size() > 0) {
				openDataBase();
				for (MSTGroupPriceTable item : rows) {
					ContentValues valInsert = new ContentValues();
					valInsert.put("Code", item.Code);
					valInsert.put("GroupCode", item.GroupCode);
					valInsert.put("Currency", item.Currency);
					valInsert.put("SellingPrice", item.SellingPrice);
					valInsert.put("SellingDiscount", item.SellingDiscount);
					valInsert.put("LastSellPrice", item.LastSellPrice);
					valInsert.put("ReservedField1", item.ReservedField1);
					valInsert.put("ReservedField2", item.ReservedField2);
					valInsert.put("ReservedField3", item.ReservedField3);
					valInsert.put("ReservedField4", item.ReservedField4);
					valInsert.put("ReservedField5", item.ReservedField5);
					myDataBase.insert("MSTGroupPriceTable", null, valInsert);
				}

				close();
			}
		} catch (Exception ex) {
			close();
			throw ex;
		}
	}

	public MSTGroupPriceTable GetGroupPriceTableByCodeGroupCurrency(
			String _Code, String _Group, String _Currency) {
		MSTGroupPriceTable item = null;
		try {
			openDataBase();
			Cursor c = myDataBase
					.rawQuery(
							"Select * from MSTGroupPriceTable where Code=? and GroupCode=? and Currency=?",
							new String[] { _Code, _Group, _Currency });
			while (c.moveToNext()) {
				item = new MSTGroupPriceTable();
				item.Code = c.getString(c.getColumnIndex("Code"));
				item.GroupCode = c.getString(c.getColumnIndex("GroupCode"));
				item.Currency = c.getString(c.getColumnIndex("Currency"));
				item.SellingPrice = c.getDouble(c
						.getColumnIndex("SellingPrice"));
				item.SellingDiscount = c.getDouble(c
						.getColumnIndex("SellingDiscount"));
				item.LastSellPrice = c.getDouble(c
						.getColumnIndex("LastSellPrice"));
				item.ReservedField1 = c.getString(c
						.getColumnIndex("ReservedField1"));
				item.ReservedField2 = c.getString(c
						.getColumnIndex("ReservedField2"));
				item.ReservedField3 = c.getString(c
						.getColumnIndex("ReservedField3"));
				item.ReservedField4 = c.getString(c
						.getColumnIndex("ReservedField4"));
				item.ReservedField5 = c.getString(c
						.getColumnIndex("ReservedField5"));
			}

		} catch (Exception ex) {
			close();
			ex.printStackTrace();
		}

		return item;
	}

	public void InsertPriceTables(ArrayList<MSTPriceTable> rows)
			throws Exception {
		try {
			DeleteTable("MSTPriceTable");

			if (rows != null && rows.size() > 0) {
				openDataBase();
				for (MSTPriceTable item : rows) {
					ContentValues valInsert = new ContentValues();
					valInsert.put("Code", item.Code);
					valInsert.put("Product", item.Product);
					valInsert.put("Currency", item.Currency);
					valInsert.put("SellingPrice", item.SellingPrice);
					valInsert.put("SellingDiscount", item.SellingDiscount);
					valInsert.put("LastSellPrice", item.LastSellPrice);
					valInsert.put("ProductSubCode", item.ProductSubCode);
					valInsert.put("UOM", item.UOM);
					valInsert.put("TrnNo", item.TrnNo);
					valInsert.put("SrNo", item.SrNo);
					valInsert.put("DealerPrice", item.DealerPrice);
					valInsert.put("RetailPrice", item.RetailPrice);
					valInsert.put("ListPrice", item.ListPrice);
					valInsert.put("PromotionPrice", item.PromotionPrice);
					valInsert.put("ReservedField1", item.ReservedField1);
					valInsert.put("ReservedField2", item.ReservedField2);
					valInsert.put("ReservedField3", item.ReservedField3);
					valInsert.put("ReservedField4", item.ReservedField4);
					valInsert.put("ReservedField5", item.ReservedField5);
					myDataBase.insert("MSTPriceTable", null, valInsert);
				}

				close();
			}
		} catch (Exception ex) {
			close();
			throw ex;
		}
	}

	public MSTPriceTable GetPriceTableByCodeProductCurrencyProductsubCodeUOM(
			String _Code, String _Product, String _Currency,
			String _ProductSubCode, String _UOM) {
		MSTPriceTable item = null;
		try {
			openDataBase();
			Cursor c = myDataBase
					.rawQuery(
							"Select * from MSTPriceTable where Code=? and Product=? and Currency=? and ProductSubCode=? and UOM=? ",
							new String[] { _Code, _Product, _Currency,
									_ProductSubCode, _UOM });
			while (c.moveToNext()) {
				item = new MSTPriceTable();
				item.Code = c.getString(c.getColumnIndex("Code"));
				item.Product = c.getString(c.getColumnIndex("Product"));
				item.Currency = c.getString(c.getColumnIndex("Currency"));
				item.SellingPrice = c.getDouble(c
						.getColumnIndex("SellingPrice"));
				item.SellingDiscount = c.getDouble(c
						.getColumnIndex("SellingDiscount"));
				item.LastSellPrice = c.getDouble(c
						.getColumnIndex("LastSellPrice"));
				item.ProductSubCode = c.getString(c
						.getColumnIndex("ProductSubCode"));
				item.UOM = c.getString(c.getColumnIndex("UOM"));
				item.TrnNo = c.getInt(c.getColumnIndex("TrnNo"));
				item.SrNo = c.getInt(c.getColumnIndex("SrNo"));
				item.DealerPrice = c.getDouble(c.getColumnIndex("DealerPrice"));
				item.RetailPrice = c.getDouble(c.getColumnIndex("RetailPrice"));
				item.ListPrice = c.getDouble(c.getColumnIndex("ListPrice"));
				item.PromotionPrice = c.getDouble(c
						.getColumnIndex("PromotionPrice"));
				item.ReservedField1 = c.getString(c
						.getColumnIndex("ReservedField1"));
				item.ReservedField2 = c.getString(c
						.getColumnIndex("ReservedField2"));
				item.ReservedField3 = c.getString(c
						.getColumnIndex("ReservedField3"));
				item.ReservedField4 = c.getString(c
						.getColumnIndex("ReservedField4"));
				item.ReservedField5 = c.getString(c
						.getColumnIndex("ReservedField5"));

			}

		} catch (Exception ex) {
			close();
			ex.printStackTrace();
		}

		return item;
	}

	public void InsertProductGroups(ArrayList<SUProductGroup> rows)
			throws Exception {
		try {
			DeleteTable("SUProductGroup");

			if (rows != null && rows.size() > 0) {
				openDataBase();
				for (SUProductGroup item : rows) {
					ContentValues valInsert = new ContentValues();
					valInsert.put("Code", item.Code);
					valInsert.put("Description", item.Description);
					valInsert.put("ReservedField1", item.ReservedField1);
					valInsert.put("ReservedField2", item.ReservedField2);
					valInsert.put("ReservedField3", item.ReservedField3);
					valInsert.put("ReservedField4", item.ReservedField4);
					valInsert.put("ReservedField5", item.ReservedField5);
					myDataBase.insert("SUProductGroup", null, valInsert);
				}

				close();
			}
		} catch (Exception ex) {
			close();
			throw ex;
		}
	}

	public SUProductGroup GetProductGroupByCode(String _Code) {
		SUProductGroup item = null;
		try {
			openDataBase();
			Cursor c = myDataBase.rawQuery(
					"Select * from SUProductGroup where Code=? ",
					new String[] { _Code });
			while (c.moveToNext()) {
				item = new SUProductGroup();
				item.Code = c.getString(c.getColumnIndex("Code"));
				item.Description = c.getString(c.getColumnIndex("Description"));
				item.ReservedField1 = c.getString(c
						.getColumnIndex("ReservedField1"));
				item.ReservedField2 = c.getString(c
						.getColumnIndex("ReservedField2"));
				item.ReservedField3 = c.getString(c
						.getColumnIndex("ReservedField3"));
				item.ReservedField4 = c.getString(c
						.getColumnIndex("ReservedField4"));
				item.ReservedField5 = c.getString(c
						.getColumnIndex("ReservedField5"));
			}

		} catch (Exception ex) {
			close();
			ex.printStackTrace();
		}

		return item;
	}

	public void InsertProducts(ArrayList<MSTProduct> rows) throws Exception {
		try {

			DeleteTable("MSTProduct");
			if (rows != null && rows.size() > 0) {
				openDataBase();
				for (MSTProduct item : rows) {
					ContentValues valInsert = new ContentValues();
					valInsert.put("ProductID", item.ProductID);
					valInsert.put("Product", item.Product);
					valInsert.put("Barcode", item.Barcode);
					valInsert.put("PName", item.PName);
					valInsert.put("Type", item.Type);
					valInsert.put("Usage", item.Usage);
					valInsert.put("Size", item.Size);
					valInsert.put("Brand", item.Brand);
					valInsert.put("Made", item.Made);
					valInsert.put("Model", item.Model); 
					valInsert.put("Class1", item.Class1);
					valInsert.put("Class2", item.Class2);
					valInsert.put("Class3", item.Class3);
					valInsert.put("Remarks", item.Remarks);
					valInsert.put("StockUnit", item.StockUnit);
					valInsert.put("SalesUnit", item.SalesUnit);
					valInsert.put("OnHandQuantity", item.OnHandQuantity);
					valInsert.put("OnOrderQuantity", item.OnOrderQuantity);
					valInsert.put("OnPOQuantity", item.OnPOQuantity);
					valInsert.put("MinStockLevel", item.MinStockLevel);
					valInsert.put("MinStockLevel", item.MinStockLevel);
					valInsert.put("MaxStockLevel", item.MaxStockLevel);
					valInsert.put("ReorderLevel", item.ReorderLevel);
					valInsert.put("OrderQuantity", item.OrderQuantity);
					valInsert.put("PriceType", item.PriceType);
					valInsert.put("Currency", item.Currency);
					valInsert.put("DealerPrice", item.DealerPrice);
					valInsert.put("RetailPrice", item.RetailPrice);
					valInsert.put("ListPrice", item.ListPrice);
					valInsert.put("PromotionPrice", item.PromotionPrice);
					valInsert.put("MinimumPrice", item.MinimumPrice);
					valInsert.put("LastSellPrice", item.LastSellPrice);
					valInsert.put("ConvProd", item.ConvProd);
					valInsert.put("NotActive", item.NotActive);
					valInsert.put("DetailMemo", item.DetailMemo);
					valInsert.put("ReservedField1", item.ReservedField1);
					valInsert.put("ReservedField2", item.ReservedField2);
					valInsert.put("ReservedField2", item.ReservedField3);
					valInsert.put("ReservedField4", item.ReservedField4);
					valInsert.put("ReservedField5", item.ReservedField5);
					myDataBase.insert("MSTProduct", null, valInsert);
				}

				close();
			}
		} catch (Exception ex) {
			close();
			throw ex;
		}
	}

	public MSTProduct GetProductByCode(String _Product) {
		MSTProduct item = null;
		try {
			openDataBase();
			Cursor c = myDataBase.rawQuery(
					"Select * from MSTProduct where Product=? ",
					new String[] { _Product });
			while (c.moveToNext()) {
				item = new MSTProduct();
				item.ProductID = c.getInt(c.getColumnIndex("ProductID"));
				item.Product = c.getString(c.getColumnIndex("Product"));
				item.Barcode = c.getString(c.getColumnIndex("Barcode"));
				item.PName = c.getString(c.getColumnIndex("PName"));
				item.Type = c.getString(c.getColumnIndex("Type"));
				item.Usage = c.getString(c.getColumnIndex("Usage"));
				item.Size = c.getString(c.getColumnIndex("Size"));
				item.Brand = c.getString(c.getColumnIndex("Brand"));
				item.Made = c.getString(c.getColumnIndex("Made"));
				item.Model= c.getString(c.getColumnIndex("Model"));
				item.Class1 = c.getString(c.getColumnIndex("Class1"));
				item.Class2 = c.getString(c.getColumnIndex("Class2"));
				item.Class3 = c.getString(c.getColumnIndex("Class3"));
				item.Remarks = c.getString(c.getColumnIndex("Remarks"));
				item.StockUnit = c.getString(c.getColumnIndex("StockUnit"));
				item.SalesUnit = c.getString(c.getColumnIndex("SalesUnit"));
				item.OnHandQuantity = c.getDouble(c
						.getColumnIndex("OnHandQuantity"));
				item.OnOrderQuantity = c.getDouble(c
						.getColumnIndex("OnOrderQuantity"));
				item.OnPOQuantity = c.getDouble(c
						.getColumnIndex("OnPOQuantity"));
				item.MinStockLevel = c.getDouble(c
						.getColumnIndex("MinStockLevel"));
				item.MaxStockLevel = c.getDouble(c
						.getColumnIndex("MaxStockLevel"));
				item.ReorderLevel = c.getDouble(c
						.getColumnIndex("ReorderLevel"));
				item.OrderQuantity = c.getDouble(c
						.getColumnIndex("OrderQuantity"));
				item.PriceType = c.getString(c.getColumnIndex("PriceType"));
				item.Currency = c.getString(c.getColumnIndex("Currency"));
				item.DealerPrice = c.getDouble(c.getColumnIndex("DealerPrice"));
				item.RetailPrice = c.getDouble(c.getColumnIndex("RetailPrice"));
				item.ListPrice = c.getDouble(c.getColumnIndex("ListPrice"));
				item.PromotionPrice = c.getDouble(c
						.getColumnIndex("PromotionPrice"));
				item.MinimumPrice = c.getDouble(c
						.getColumnIndex("MinimumPrice"));
				item.LastSellPrice = c.getDouble(c
						.getColumnIndex("LastSellPrice"));
				item.ConvProd = c.getString(c.getColumnIndex("ConvProd"));
				item.DetailMemo = c.getString(c.getColumnIndex("DetailMemo"));
				item.NotActive = Boolean.valueOf(c.getString(c
						.getColumnIndex("NotActive")));
				item.ReservedField1 = c.getString(c
						.getColumnIndex("ReservedField1"));
				item.ReservedField2 = c.getString(c
						.getColumnIndex("ReservedField2"));
				item.ReservedField3 = c.getString(c
						.getColumnIndex("ReservedField3"));
				item.ReservedField4 = c.getString(c
						.getColumnIndex("ReservedField4"));
				item.ReservedField5 = c.getString(c
						.getColumnIndex("ReservedField5"));
			}

		} catch (Exception ex) {
			close();
			ex.printStackTrace();
		}

		return item;
	}

	public ArrayList<MSTProduct> GetAllProducts(boolean _short)
			throws Exception {
		ArrayList<MSTProduct> items = new ArrayList<MSTProduct>();
		try {
			openDataBase();
			String cols = _short ? "Product,PName,Brand,Made,Model" : "*";
			Cursor c = myDataBase.rawQuery("Select " + cols
					+ " from MSTProduct ", null);
			while (c.moveToNext()) {
				MSTProduct item = new MSTProduct();
				item.Product = c.getString(c.getColumnIndex("Product"));
				item.PName = c.getString(c.getColumnIndex("PName"));
				item.Brand= c.getString(c.getColumnIndex("Brand"));
				item.Made= c.getString(c.getColumnIndex("Made"));
				item.Model= c.getString(c.getColumnIndex("Model"));
				if (!_short) {
					item.ProductID = c.getInt(c.getColumnIndex("ProductID"));
					item.Barcode = c.getString(c.getColumnIndex("Barcode"));
					item.Type = c.getString(c.getColumnIndex("Type"));
					item.Usage = c.getString(c.getColumnIndex("Usage"));
					item.Size = c.getString(c.getColumnIndex("Size")); 
					item.Class1 = c.getString(c.getColumnIndex("Class1"));
					item.Class2 = c.getString(c.getColumnIndex("Class2"));
					item.Class3 = c.getString(c.getColumnIndex("Class3"));
					item.Remarks = c.getString(c.getColumnIndex("Remarks"));
					item.StockUnit = c.getString(c.getColumnIndex("StockUnit"));
					item.SalesUnit = c.getString(c.getColumnIndex("SalesUnit"));
					item.OnHandQuantity = c.getDouble(c
							.getColumnIndex("OnHandQuantity"));
					item.OnOrderQuantity = c.getDouble(c
							.getColumnIndex("OnOrderQuantity"));
					item.OnPOQuantity = c.getDouble(c
							.getColumnIndex("OnPOQuantity"));
					item.MinStockLevel = c.getDouble(c
							.getColumnIndex("MinStockLevel"));
					item.MaxStockLevel = c.getDouble(c
							.getColumnIndex("MaxStockLevel"));
					item.ReorderLevel = c.getDouble(c
							.getColumnIndex("ReorderLevel"));
					item.OrderQuantity = c.getDouble(c
							.getColumnIndex("OrderQuantity"));
					item.PriceType = c.getString(c.getColumnIndex("PriceType"));
					item.Currency = c.getString(c.getColumnIndex("Currency"));
					item.DealerPrice = c.getDouble(c
							.getColumnIndex("DealerPrice"));
					item.RetailPrice = c.getDouble(c
							.getColumnIndex("RetailPrice"));
					item.ListPrice = c.getDouble(c.getColumnIndex("ListPrice"));
					item.PromotionPrice = c.getDouble(c
							.getColumnIndex("PromotionPrice"));
					item.MinimumPrice = c.getDouble(c
							.getColumnIndex("MinimumPrice"));
					item.LastSellPrice = c.getDouble(c
							.getColumnIndex("LastSellPrice"));
					item.NotActive = Boolean.valueOf(c.getString(c
							.getColumnIndex("NotActive")));
					item.ReservedField1 = c.getString(c
							.getColumnIndex("ReservedField1"));
					item.ReservedField2 = c.getString(c
							.getColumnIndex("ReservedField2"));
					item.ReservedField3 = c.getString(c
							.getColumnIndex("ReservedField3"));
					item.ReservedField4 = c.getString(c
							.getColumnIndex("ReservedField4"));
					item.ReservedField5 = c.getString(c
							.getColumnIndex("ReservedField5"));
				}
				items.add(item);
			}
			/*
			 * String JsonString = CursorToString(c); if (!JsonString.equals("")
			 * && !JsonString.equals("[]")) { JsonString =
			 * "{\"MSTProductfields\":" + JsonString + "}"; MSTProductHolder
			 * data = new Gson().fromJson(JsonString, MSTProductHolder.class);
			 * items = new ArrayList<MSTProduct>(
			 * Arrays.asList(data.MSTProductfields)); } close();
			 */
			close();
		} catch (Exception ex) {
			close();
			throw ex;
		}
		return items;
	}

	public ArrayList<MSTProduct> SearchProducts(String _text, boolean _short)
			throws Exception {
		ArrayList<MSTProduct> items = new ArrayList<MSTProduct>();
		try {
			openDataBase();
			String cols = _short ? "Product,PName" : "*";
			Cursor c = myDataBase.rawQuery("Select " + cols
					+ " from MSTProduct where LOWER(Product) like '%" + _text
					+ "%' or LOWER(PName) like '%" + _text + "%'", null);
			while (c.moveToNext()) {
				MSTProduct item = new MSTProduct();
				item.Product = c.getString(c.getColumnIndex("Product"));
				item.PName = c.getString(c.getColumnIndex("PName"));
				item.Brand= c.getString(c.getColumnIndex("Brand"));
				item.Made= c.getString(c.getColumnIndex("Made"));
				item.Model= c.getString(c.getColumnIndex("Model"));
				if (!_short) {
					item.ProductID = c.getInt(c.getColumnIndex("ProductID"));
					item.Barcode = c.getString(c.getColumnIndex("Barcode"));
					item.Type = c.getString(c.getColumnIndex("Type"));
					item.Usage = c.getString(c.getColumnIndex("Usage"));
					item.Size = c.getString(c.getColumnIndex("Size")); 
					item.Class1 = c.getString(c.getColumnIndex("Class1"));
					item.Class2 = c.getString(c.getColumnIndex("Class2"));
					item.Class3 = c.getString(c.getColumnIndex("Class3"));
					item.Remarks = c.getString(c.getColumnIndex("Remarks"));
					item.StockUnit = c.getString(c.getColumnIndex("StockUnit"));
					item.SalesUnit = c.getString(c.getColumnIndex("SalesUnit"));
					item.OnHandQuantity = c.getDouble(c
							.getColumnIndex("OnHandQuantity"));
					item.OnOrderQuantity = c.getDouble(c
							.getColumnIndex("OnOrderQuantity"));
					item.OnPOQuantity = c.getDouble(c
							.getColumnIndex("OnPOQuantity"));
					item.MinStockLevel = c.getDouble(c
							.getColumnIndex("MinStockLevel"));
					item.MaxStockLevel = c.getDouble(c
							.getColumnIndex("MaxStockLevel"));
					item.ReorderLevel = c.getDouble(c
							.getColumnIndex("ReorderLevel"));
					item.OrderQuantity = c.getDouble(c
							.getColumnIndex("OrderQuantity"));
					item.PriceType = c.getString(c.getColumnIndex("PriceType"));
					item.Currency = c.getString(c.getColumnIndex("Currency"));
					item.DealerPrice = c.getDouble(c
							.getColumnIndex("DealerPrice"));
					item.RetailPrice = c.getDouble(c
							.getColumnIndex("RetailPrice"));
					item.ListPrice = c.getDouble(c.getColumnIndex("ListPrice"));
					item.PromotionPrice = c.getDouble(c
							.getColumnIndex("PromotionPrice"));
					item.MinimumPrice = c.getDouble(c
							.getColumnIndex("MinimumPrice"));
					item.LastSellPrice = c.getDouble(c
							.getColumnIndex("LastSellPrice"));
					item.NotActive = Boolean.valueOf(c.getString(c
							.getColumnIndex("NotActive")));
					item.ReservedField1 = c.getString(c
							.getColumnIndex("ReservedField1"));
					item.ReservedField2 = c.getString(c
							.getColumnIndex("ReservedField2"));
					item.ReservedField3 = c.getString(c
							.getColumnIndex("ReservedField3"));
					item.ReservedField4 = c.getString(c
							.getColumnIndex("ReservedField4"));
					item.ReservedField5 = c.getString(c
							.getColumnIndex("ReservedField5"));
				}
				items.add(item);
			}

			close();
		} catch (Exception ex) {
			close();
			throw ex;
		}
		return items;
	}

	public void InsertUnitConversionByProducts(
			ArrayList<SMUnitConversionProd> rows) throws Exception {
		try {
			DeleteTable("SMUnitConversionProd");

			if (rows != null && rows.size() > 0) {
				openDataBase();
				for (SMUnitConversionProd item : rows) {
					ContentValues valInsert = new ContentValues();
					valInsert.put("Product", item.Product);
					valInsert.put("Code", item.Code);
					valInsert.put("ToUnit", item.ToUnit);
					valInsert.put("ConversionFactor", item.ConversionFactor);
					valInsert.put("UseConvFact", item.UseConvFact);
					valInsert.put("PriceRev", item.PriceRev);
					valInsert.put("ReservedField1", item.ReservedField1);
					valInsert.put("ReservedField2", item.ReservedField2);
					valInsert.put("ReservedField3", item.ReservedField3);
					valInsert.put("ReservedField4", item.ReservedField4);
					valInsert.put("ReservedField5", item.ReservedField5);
					myDataBase.insert("SMUnitConversionProd", null, valInsert);
				}

				close();
			}
		} catch (Exception ex) {
			close();
			throw ex;
		}
	}

	public SMUnitConversionProd GetConversionByProductByProductCodeToUnit(
			String _Product, String _Code, String _ToUnit) {
		SMUnitConversionProd item = null;
		try {
			openDataBase();
			Cursor c = myDataBase
					.rawQuery(
							"Select * from SMUnitConversionProd where Product=? and Code=? and ToUnit=? ",
							new String[] { _Product, _Code, _ToUnit });
			while (c.moveToNext()) {
				item = new SMUnitConversionProd();
				item.Product = c.getString(c.getColumnIndex("Product"));
				item.Code = c.getString(c.getColumnIndex("Code"));
				item.ToUnit = c.getString(c.getColumnIndex("ToUnit"));
				item.ConversionFactor = c.getDouble(c
						.getColumnIndex("ConversionFactor"));
				item.UseConvFact = c.getString(c.getColumnIndex("UseConvFact"));
				item.PriceRev = c.getString(c.getColumnIndex("PriceRev"));
				item.ReservedField1 = c.getString(c
						.getColumnIndex("ReservedField1"));
				item.ReservedField2 = c.getString(c
						.getColumnIndex("ReservedField2"));
				item.ReservedField3 = c.getString(c
						.getColumnIndex("ReservedField3"));
				item.ReservedField4 = c.getString(c
						.getColumnIndex("ReservedField4"));
				item.ReservedField5 = c.getString(c
						.getColumnIndex("ReservedField5"));
			}

		} catch (Exception ex) {
			close();
			ex.printStackTrace();
		}

		return item;
	}

	public void InsertUnitofMeasure(ArrayList<SMUnitofMeasure> rows)
			throws Exception {
		try {
			DeleteTable("SMUnitofMeasure");

			if (rows != null && rows.size() > 0) {
				openDataBase();
				for (SMUnitofMeasure item : rows) {
					ContentValues valInsert = new ContentValues();
					valInsert.put("Code", item.Code);
					valInsert.put("Description", item.Description);
					valInsert.put("ReservedField1", item.ReservedField1);
					valInsert.put("ReservedField2", item.ReservedField2);
					valInsert.put("ReservedField3", item.ReservedField3);
					valInsert.put("ReservedField4", item.ReservedField4);
					valInsert.put("ReservedField5", item.ReservedField5);
					myDataBase.insert("SMUnitofMeasure", null, valInsert);
				}

				close();
			}
		} catch (Exception ex) {
			close();
			throw ex;
		}
	}

	public SMUnitofMeasure GetUnitofMeasureByCode(String _Code) {
		SMUnitofMeasure item = null;
		try {
			openDataBase();
			Cursor c = myDataBase.rawQuery(
					"Select * from SMUnitofMeasure where Code=?",
					new String[] { _Code });
			while (c.moveToNext()) {
				item = new SMUnitofMeasure();
				item.Code = c.getString(c.getColumnIndex("Code"));
				item.Description = c.getString(c.getColumnIndex("Description"));
				item.ReservedField1 = c.getString(c
						.getColumnIndex("ReservedField1"));
				item.ReservedField2 = c.getString(c
						.getColumnIndex("ReservedField2"));
				item.ReservedField3 = c.getString(c
						.getColumnIndex("ReservedField3"));
				item.ReservedField4 = c.getString(c
						.getColumnIndex("ReservedField4"));
				item.ReservedField5 = c.getString(c
						.getColumnIndex("ReservedField5"));
			}

		} catch (Exception ex) {
			close();
			ex.printStackTrace();
		}

		return item;
	}

	public void InsertSystemSettings(ArrayList<MSTSettings> rows)
			throws Exception {
		try {
			DeleteTable("MSTSettings");
			if (rows != null && rows.size() > 0) {
				openDataBase();
				for (MSTSettings item : rows) {
					ContentValues valInsert = new ContentValues();
					valInsert.put("ID", item.ID);
					valInsert.put("SName", item.SName);
					valInsert.put("A21Code", item.A21Code);
					valInsert.put("Add1", item.Add1);
					valInsert.put("Add2", item.Add2);
					valInsert.put("Add3", item.Add3);
					valInsert.put("Add4", item.Add4);
					valInsert.put("Year", item.Year);
					valInsert.put("CostType", item.CostType);
					valInsert.put("YPType", item.YPType);
					valInsert.put("TYear", item.TYear);
					valInsert.put("TMonth", item.TMonth);
					valInsert.put("TWeek", item.TWeek);
					valInsert.put("FYStart", item.FYStart);
					valInsert.put("NoPeriod", item.NoPeriod);
					valInsert.put("A21Close", item.A21Close);
					valInsert.put("CYear", item.CYear);
					valInsert.put("CMonth", item.CMonth);
					valInsert.put("FDate", item.FDate);
					valInsert.put("TDate", item.TDate);
					valInsert.put("BaseCurrency", item.BaseCurrency);
					valInsert.put("GSTAccountIN", item.GSTAccountIN);
					valInsert.put("GSTAccountOUT", item.GSTAccountOUT);
					valInsert.put("SOAtype", item.SOAtype);
					valInsert.put("SOAtype", item.SOAtype);
					valInsert.put("PrinterName", item.PrinterName);
					valInsert.put("PrintHeader1", item.PrintHeader1);
					valInsert.put("PrintHeader1IsBold",
							item.PrintHeader1IsBold ? "1" : "0");
					valInsert.put("PrintHeader2", item.PrintHeader2);
					valInsert.put("PrintHeader2IsBold",
							item.PrintHeader2IsBold ? "1" : "0");
					valInsert.put("PrintHeader3", item.PrintHeader3);
					valInsert.put("PrintHeader3IsBold",
							item.PrintHeader3IsBold ? "1" : "0");
					valInsert.put("PrintHeader4", item.PrintHeader4);
					valInsert.put("PrintHeader4IsBold",
							item.PrintHeader4IsBold ? "1" : "0");
					valInsert.put("PrintHeader5", item.PrintHeader5);
					valInsert.put("PrintHeader5IsBold",
							item.PrintHeader5IsBold ? "1" : "0");
					
					valInsert.put("PrintHeader6", item.PrintHeader6);
					valInsert.put("PrintHeader6IsBold",
							item.PrintHeader6IsBold ? "1" : "0");
					
					valInsert.put("PrintHeader7", item.PrintHeader7);
					valInsert.put("PrintHeader7IsBold",
							item.PrintHeader7IsBold ? "1" : "0");
					
					valInsert.put("PrintFooter1", item.PrintFooter1);
					valInsert.put("PrintFooter1IsBold",
							item.PrintFooter1IsBold ? "1" : "0");
					valInsert.put("PrintFooter2", item.PrintFooter2);
					valInsert.put("PrintFooter2IsBold",
							item.PrintFooter2IsBold ? "1" : "0");
					valInsert.put("PrintFooter3", item.PrintFooter3);
					valInsert.put("PrintFooter3IsBold",
							item.PrintFooter3IsBold ? "1" : "0");
					valInsert.put("PrintFooter4", item.PrintFooter4);
					valInsert.put("PrintFooter4IsBold",
							item.PrintFooter4IsBold ? "1" : "0");
					valInsert.put("PrintFooter5", item.PrintFooter5);
					valInsert.put("PrintFooter5IsBold",
							item.PrintFooter5IsBold ? "1" : "0");
					valInsert.put("PrintFooter6", item.PrintFooter6);
					valInsert.put("PrintFooter6IsBold",
							item.PrintFooter6IsBold ? "1" : "0");

					valInsert.put("PrintFooter7", item.PrintFooter7);
					valInsert.put("PrintFooter7IsBold",
							item.PrintFooter7IsBold ? "1" : "0");
					
					valInsert.put("PriceLock", item.PriceLock ? "1" : "0"); 

					valInsert
							.put("DiscountLock", item.DiscountLock ? "1" : "0");
					
					valInsert.put("PrintDO_CA", item.PrintDO_CA ? "1" : "0");
					valInsert.put("PrintDO_CR", item.PrintDO_CR ? "1" : "0");
					valInsert.put("PrintDO_CW", item.PrintDO_CW ? "1" : "0");
					valInsert.put("GlobalDiscount", item.GlobalDiscount ? "1" : "0");
					valInsert.put("LineDiscount", item.LineDiscount ? "1" : "0"); 
					valInsert.put("PrintLayout", item.PrintLayout);
					 
					valInsert.put("NoOfReceiptCW", item.NoOfReceiptCW);
					valInsert.put("NoOfReceiptCA", item.NoOfReceiptCA);
					valInsert.put("NoOfReceiptCR", item.NoOfReceiptCR);
					 
					myDataBase.insert("MSTSettings", null, valInsert);
				}

				close();
			}
		} catch (Exception ex) {
			close();
			throw ex;
		}
	}

	public void UpdateSystemImage(byte[] logo,String name) throws Exception {
		try {
			MSTSettings setting = GetSystemSettingsById("1");
			openDataBase();
			if (setting != null) {
				ContentValues valUpdate = new ContentValues();
				valUpdate.put( name, logo);
				myDataBase.update("MSTSettings", valUpdate, "Id=?",
						new String[] { "1" });
			}
			close();
		} catch (Exception ex) {
			close();
			throw ex;
		}
	}
	
	//SystemLogo,PrintHeaderLogo,PrintFooterLogo 
	 
	public byte[] getImageLogo(String name) {
		byte[] retval = null;
		try {
			openDataBase();
			Cursor c = myDataBase.rawQuery(
					"Select "+ name +" from MSTSettings where ID=1", null);
			while (c.moveToNext()) {
				retval = c.getBlob(0);
			}
			close();
		} catch (Exception e) {
			close();
			int i = 0;
		}
		return retval;
	}

	public MSTSettings GetSystemSettingsById(String _ID) {
		MSTSettings item = null;
		try {
			openDataBase();
			Cursor c = myDataBase.rawQuery(
					"Select * from MSTSettings where ID=?",
					new String[] { _ID });
			while (c.moveToNext()) {
				item = new MSTSettings();
				item.ID = c.getInt(c.getColumnIndex("ID"));
				item.SName = c.getString(c.getColumnIndex("SName"));
				item.A21Code = c.getString(c.getColumnIndex("A21Code"));
				item.Add1 = c.getString(c.getColumnIndex("Add1"));
				item.Add2 = c.getString(c.getColumnIndex("Add2"));
				item.Add3 = c.getString(c.getColumnIndex("Add3"));
				item.Add4 = c.getString(c.getColumnIndex("Add4"));
				item.Year = c.getString(c.getColumnIndex("Year"));
				item.CostType = c.getString(c.getColumnIndex("CostType"));
				item.YPType = c.getString(c.getColumnIndex("YPType"));
				item.TYear = c.getString(c.getColumnIndex("TYear"));
				item.TMonth = c.getString(c.getColumnIndex("TMonth"));
				item.TWeek = c.getString(c.getColumnIndex("TWeek"));
				item.FYStart = c.getString(c.getColumnIndex("FYStart"));
				item.NoPeriod = c.getString(c.getColumnIndex("NoPeriod"));
				item.A21Close = c.getString(c.getColumnIndex("A21Close"));
				item.CYear = c.getString(c.getColumnIndex("CYear"));
				item.CMonth = c.getString(c.getColumnIndex("CMonth"));
				item.FDate = c.getString(c.getColumnIndex("FDate"));
				item.TDate = c.getString(c.getColumnIndex("TDate"));
				item.BaseCurrency = c.getString(c
						.getColumnIndex("BaseCurrency"));
				item.GSTAccountIN = c.getString(c
						.getColumnIndex("GSTAccountIN"));
				item.GSTAccountOUT = c.getString(c
						.getColumnIndex("GSTAccountOUT"));
				item.SOAtype = c.getString(c.getColumnIndex("SOAtype"));
				item.PrinterName = c.getString(c.getColumnIndex("PrinterName"));
				item.PrintHeader1 = c.getString(c
						.getColumnIndex("PrintHeader1"));
				item.PrintHeader1IsBold = c.getInt(c
						.getColumnIndex("PrintHeader1IsBold")) > 0;
				item.PrintHeader2 = c.getString(c
						.getColumnIndex("PrintHeader2"));
				item.PrintHeader2IsBold = c.getInt(c
						.getColumnIndex("PrintHeader2IsBold")) > 0;
				item.PrintHeader3 = c.getString(c
						.getColumnIndex("PrintHeader3"));
				item.PrintHeader3IsBold = c.getInt(c
						.getColumnIndex("PrintHeader3IsBold")) > 0;
				item.PrintHeader4 = c.getString(c
						.getColumnIndex("PrintHeader4"));
				item.PrintHeader4IsBold = c.getInt(c
						.getColumnIndex("PrintHeader4IsBold")) > 0;
				item.PrintHeader5 = c.getString(c
						.getColumnIndex("PrintHeader5"));
				item.PrintHeader5IsBold = c.getInt(c
						.getColumnIndex("PrintHeader5IsBold")) > 0;
				item.PrintHeader6 = c.getString(c
						.getColumnIndex("PrintHeader6"));
				item.PrintHeader6IsBold = c.getInt(c
						.getColumnIndex("PrintHeader6IsBold")) > 0;
						
						item.PrintHeader7 = c.getString(c
								.getColumnIndex("PrintHeader7"));
						item.PrintHeader7IsBold = c.getInt(c
								.getColumnIndex("PrintHeader7IsBold")) > 0;
						
				item.PrintFooter1 = c.getString(c
						.getColumnIndex("PrintFooter1"));
				item.PrintFooter1IsBold = c.getInt(c
						.getColumnIndex("PrintFooter1IsBold")) > 0;
				item.PrintFooter2 = c.getString(c
						.getColumnIndex("PrintFooter2"));
				item.PrintFooter2IsBold = c.getInt(c
						.getColumnIndex("PrintFooter2IsBold")) > 0;
				item.PrintFooter3 = c.getString(c
						.getColumnIndex("PrintFooter3"));
				item.PrintFooter3IsBold = c.getInt(c
						.getColumnIndex("PrintFooter3IsBold")) > 0;
				item.PrintFooter4 = c.getString(c
						.getColumnIndex("PrintFooter4"));
				item.PrintFooter4IsBold = c.getInt(c
						.getColumnIndex("PrintFooter4IsBold")) > 0;
				item.PrintFooter5 = c.getString(c
						.getColumnIndex("PrintFooter5"));
				item.PrintFooter5IsBold = c.getInt(c
						.getColumnIndex("PrintFooter5IsBold")) > 0;
				item.PrintFooter6 = c.getString(c
						.getColumnIndex("PrintFooter6"));
				item.PrintFooter6IsBold = c.getInt(c
						.getColumnIndex("PrintFooter6IsBold")) > 0;

						item.PrintFooter7 = c.getString(c
								.getColumnIndex("PrintFooter7"));
						item.PrintFooter7IsBold = c.getInt(c
								.getColumnIndex("PrintFooter7IsBold")) > 0;
						
				item.DiscountLock = c.getInt(c.getColumnIndex("DiscountLock")) > 0;
				item.PriceLock = c.getInt(c.getColumnIndex("PriceLock")) > 0; 
				item.PrintDO_CA = c.getInt(c.getColumnIndex("PrintDO_CA")) > 0;
				item.PrintDO_CR = c.getInt(c.getColumnIndex("PrintDO_CR")) > 0;
				item.PrintDO_CW = c.getInt(c.getColumnIndex("PrintDO_CW")) > 0;
				 
				item.GlobalDiscount = c.getInt(c.getColumnIndex("GlobalDiscount")) > 0;
				item.LineDiscount = c.getInt(c.getColumnIndex("LineDiscount")) > 0; 
				
				item.PrintLayout = c.getString(c.getColumnIndex("PrintLayout")); 
				/*item.ReservedField1 = c.getString(c
						.getColumnIndex("ReservedField1"));
				item.ReservedField2 = c.getString(c
						.getColumnIndex("ReservedField2"));
				item.ReservedField3 = c.getString(c
						.getColumnIndex("ReservedField3"));
				item.ReservedField4 = c.getString(c
						.getColumnIndex("ReservedField4"));
				item.ReservedField5 = c.getString(c
						.getColumnIndex("ReservedField5"));*/
				
				item.NoOfReceiptCW = c.getInt(c.getColumnIndex("NoOfReceiptCW"))  ;
				item.NoOfReceiptCA = c.getInt(c.getColumnIndex("NoOfReceiptCA")) ;
				item.NoOfReceiptCR = c.getInt(c.getColumnIndex("NoOfReceiptCR"))  ; 
			}

		} catch (Exception ex) {
			close();
			ex.printStackTrace();
		}

		return item;
	}

	public void InsertA21OPPriceBooks(ArrayList<A21OPPriceBook> rows)
			throws Exception {
		try {
			DeleteTable("A21OPPriceBook");
			if (rows != null && rows.size() > 0) {
				openDataBase();
				for (A21OPPriceBook item : rows) {
					ContentValues valInsert = new ContentValues();
					valInsert.put("ID", item.ID);
					valInsert.put("Description", item.Description);
					valInsert.put("Currency", item.Currency);
					myDataBase.insert("A21OPPriceBook", null, valInsert);
				}
				close();
			}
		} catch (Exception ex) {
			close();
			throw ex;
		}
	}

	public void InsertA21OPPriceBookDetails(ArrayList<A21OPPriceBookDetail> rows)
			throws Exception {
		try {
			DeleteTable("A21OPPriceBookDetail");
			if (rows != null && rows.size() > 0) {
				openDataBase();
				for (A21OPPriceBookDetail item : rows) {
					ContentValues valInsert = new ContentValues();
					valInsert.put("ID", item.ID);
					valInsert.put("PriceBookID", item.PriceBookID);
					valInsert.put("Product", item.Product);
					valInsert.put("UOM", item.UOM);
					valInsert.put("SellingPrice", item.SellingPrice);
					valInsert.put("LineDiscount1", item.LineDiscount1);
					myDataBase.insert("A21OPPriceBookDetail", null, valInsert);
				}
				close();
			}
		} catch (Exception ex) {
			close();
			throw ex;
		}
	}

	public A21OPPriceBookDetail GetPriceBookDetailByPriceBookIDProductUOMCurrency(
			String _PriceBookID, String _Product, String _UOM, String _Currency) {
		A21OPPriceBookDetail item = null;
		try {
			openDataBase();
			Cursor c = myDataBase
					.rawQuery(
							"Select d.* from A21OPPriceBook h inner join A21OPPriceBookDetail d on h.ID=d.PriceBookID where d.PriceBookID=? and d.Product=? and d.UOM=?  and h.Currency=?",
							new String[] { _PriceBookID, _Product, _UOM,
									_Currency });
			while (c.moveToNext()) {
				item = new A21OPPriceBookDetail();
				item.ID = c.getInt(c.getColumnIndex("ID"));
				item.PriceBookID = c.getInt(c.getColumnIndex("PriceBookID"));
				item.Product = c.getString(c.getColumnIndex("Product"));
				item.UOM = c.getString(c.getColumnIndex("UOM"));
				item.SellingPrice = c.getDouble(c
						.getColumnIndex("SellingPrice"));
				item.SellingPrice = c.getDouble(c
						.getColumnIndex("SellingPrice"));
				item.LineDiscount1 = c.getDouble(c
						.getColumnIndex("LineDiscount1"));
			}

		} catch (Exception ex) {
			close();
			ex.printStackTrace();
		}

		return item;
	}

	public void InsertMSTPriceBrackets(ArrayList<MSTPriceBracket> rows)
			throws Exception {
		try {
			DeleteTable("MSTPriceBracket");
			if (rows != null && rows.size() > 0) {
				openDataBase();
				for (MSTPriceBracket item : rows) {
					ContentValues valInsert = new ContentValues();
					valInsert.put("ID", item.ID);
					valInsert.put("Product", item.Product);
					valInsert.put("Code", item.Code);
					valInsert.put("Bracket1", item.Bracket1);
					valInsert.put("Bracket2", item.Bracket2);
					valInsert.put("Bracket3", item.Bracket3);
					valInsert.put("Bracket4", item.Bracket4);
					valInsert.put("Bracket5", item.Bracket5);
					valInsert.put("Bracket1_Price", item.Bracket1_Price);
					valInsert.put("Bracket2_Price", item.Bracket2_Price);
					valInsert.put("Bracket3_Price", item.Bracket3_Price);
					valInsert.put("Bracket4_Price", item.Bracket4_Price);
					valInsert.put("Bracket5_Price", item.Bracket5_Price);
					valInsert.put("Bracket1_Remarks", item.Bracket1_Remarks);
					valInsert.put("Bracket2_Remarks", item.Bracket2_Remarks);
					valInsert.put("Bracket3_Remarks", item.Bracket3_Remarks);
					valInsert.put("Bracket4_Remarks", item.Bracket4_Remarks);
					valInsert.put("Bracket5_Remarks", item.Bracket5_Remarks);
					valInsert.put("Remarks", item.Remarks);
					valInsert.put("Type", item.Type);
					valInsert.put("UOM1", item.UOM1);
					valInsert.put("UOM2", item.UOM2);
					valInsert.put("UOM3", item.UOM3);
					valInsert.put("UOM4", item.UOM4);
					valInsert.put("UOM5", item.UOM5);
					myDataBase.insert("MSTPriceBracket", null, valInsert);
				}
				close();
			}
		} catch (Exception ex) {
			close();
			throw ex;
		}
	}

	public MSTPriceBracket GetPriceBracketByCodeProduct(String _Code,
			String _Product) {
		MSTPriceBracket item = null;
		try {
			openDataBase();
			Cursor c = myDataBase
					.rawQuery(
							"Select * from MSTPriceBracket  where Code=? and  Product=? ",
							new String[] { _Code, _Product });
			while (c.moveToNext()) {
				item = new MSTPriceBracket();
				item.ID = c.getInt(c.getColumnIndex("ID"));
				item.Product = c.getString(c.getColumnIndex("Product"));
				item.Code = c.getString(c.getColumnIndex("Code"));

				item.Bracket1 = c.getDouble(c.getColumnIndex("Bracket1"));
				item.Bracket2 = c.getDouble(c.getColumnIndex("Bracket2"));
				item.Bracket3 = c.getDouble(c.getColumnIndex("Bracket3"));
				item.Bracket4 = c.getDouble(c.getColumnIndex("Bracket4"));
				item.Bracket5 = c.getDouble(c.getColumnIndex("Bracket5"));
				item.Bracket1_Price = c.getDouble(c
						.getColumnIndex("Bracket1_Price"));
				item.Bracket2_Price = c.getDouble(c
						.getColumnIndex("Bracket2_Price"));
				item.Bracket3_Price = c.getDouble(c
						.getColumnIndex("Bracket3_Price"));
				item.Bracket4_Price = c.getDouble(c
						.getColumnIndex("Bracket4_Price"));
				item.Bracket5_Price = c.getDouble(c
						.getColumnIndex("Bracket5_Price"));
				item.Bracket1_Remarks = c.getString(c
						.getColumnIndex("Bracket1_Remarks"));
				item.Bracket2_Remarks = c.getString(c
						.getColumnIndex("Bracket2_Remarks"));
				item.Bracket3_Remarks = c.getString(c
						.getColumnIndex("Bracket3_Remarks"));
				item.Bracket4_Remarks = c.getString(c
						.getColumnIndex("Bracket4_Remarks"));
				item.Bracket5_Remarks = c.getString(c
						.getColumnIndex("Bracket5_Remarks"));
				item.Remarks = c.getString(c.getColumnIndex("Remarks"));
				item.Type = c.getString(c.getColumnIndex("Type"));
				item.UOM1 = c.getString(c.getColumnIndex("UOM1"));
				item.UOM2 = c.getString(c.getColumnIndex("UOM2"));
				item.UOM3 = c.getString(c.getColumnIndex("UOM3"));
				item.UOM4 = c.getString(c.getColumnIndex("UOM4"));
				item.UOM5 = c.getString(c.getColumnIndex("UOM5"));
			}

		} catch (Exception ex) {
			close();
			ex.printStackTrace();
		}

		return item;
	}

	public void InsertSMUnitConversions(ArrayList<SMUnitConversion> rows)
			throws Exception {
		try {
			DeleteTable("SMUnitConversion");
			if (rows != null && rows.size() > 0) {
				openDataBase();
				for (SMUnitConversion item : rows) {
					ContentValues valInsert = new ContentValues();
					valInsert.put("Code", item.Code);
					valInsert.put("ToUnit", item.ToUnit);
					valInsert.put("ConversionFactor", item.ConversionFactor);
					myDataBase.insert("SMUnitConversion", null, valInsert);
				}
				close();
			}
		} catch (Exception ex) {
			close();
			throw ex;
		}
	}

	public void InsertFrequentLists(ArrayList<FrequentPurchaseList> rows,
			String _Code) throws Exception {
		try {
			if (rows != null && rows.size() > 0) {
				openDataBase();
				for (FrequentPurchaseList item : rows) {
					ContentValues valInsert = new ContentValues();
					valInsert.put("Product", item.Product);
					valInsert.put("Code", _Code);
					valInsert.put("TrnNo", item.TrnType + "-" + item.TrnNo);
					valInsert.put("Currency", item.Currency);
					valInsert.put("PurchaseDate", item.TrnDate);
					valInsert.put("Price", item.LastSellPrice);
					myDataBase.insert("FrequentPurchaseList", null, valInsert);
				}
				close();
			}
		} catch (Exception ex) {
			close();
			throw ex;
		}
	}

	public ArrayList<FrequentPurchaseList> GetFrequentList(String _Code,
			String _KeySerch) {
		ArrayList<FrequentPurchaseList> items = new ArrayList<FrequentPurchaseList>();
		try {
			openDataBase();
			String qry = "Select FrequentPurchaseList.*,MSTProduct.PName,MSTProduct.Brand,MSTProduct.Model,MSTProduct.Made from FrequentPurchaseList"
					+ " inner join MSTProduct on FrequentPurchaseList.Product=MSTProduct.Product where FrequentPurchaseList.Code=?";
			Cursor c = null;
			if (!_KeySerch.equals("")) {
				qry = "Select FrequentPurchaseList.*,MSTProduct.PName,MSTProduct.Brand,MSTProduct.Model,MSTProduct.Made from FrequentPurchaseList"
						+ " inner join MSTProduct on FrequentPurchaseList.Product=MSTProduct.Product where FrequentPurchaseList.Code=? and LOWER(MSTProduct.PName) like ? or LOWER(FrequentPurchaseList.Product) like ?";
				c = myDataBase.rawQuery(qry, new String[] { _Code,
						"%" + _KeySerch + "%", "%" + _KeySerch + "%" });

			} else {
				c = myDataBase.rawQuery(qry, new String[] { _Code });
			}

			while (c.moveToNext()) {
				FrequentPurchaseList item = new FrequentPurchaseList();
				item.Product = c.getString(c.getColumnIndex("Product"));
				item.Code = c.getString(c.getColumnIndex("Code"));
				item.TrnNo = c.getString(c.getColumnIndex("TrnNo"));
				item.Currency = c.getString(c.getColumnIndex("Currency"));
				item.TrnDate = c.getString(c.getColumnIndex("PurchaseDate"));
				item.Currency = c.getString(c.getColumnIndex("Currency"));
				item.PName = c.getString(c.getColumnIndex("PName"));
				item.LastSellPrice = c.getDouble(c.getColumnIndex("Price"));
				item.Brand = c.getString(c.getColumnIndex("Brand"));
				item.Model = c.getString(c.getColumnIndex("Model"));
				item.Made = c.getString(c.getColumnIndex("Made"));
				items.add(item);
				close();
			}

		} catch (Exception ex) {
			close();
			ex.printStackTrace();
		}

		return items;
	}

	public SMUnitConversion GetSMUnitConversion(String _Code, String _ToCode) {
		SMUnitConversion item = null;
		try {
			openDataBase();
			Cursor c = myDataBase
					.rawQuery(
							"Select * from SMUnitConversion  where Code=? and  ToUnit=? ",
							new String[] { _Code, _ToCode });
			while (c.moveToNext()) {
				item = new SMUnitConversion();
				item.Code = c.getString(c.getColumnIndex("Code"));
				item.ToUnit = c.getString(c.getColumnIndex("ToUnit"));
				item.ConversionFactor = c.getDouble(c
						.getColumnIndex("ConversionFactor"));
			}

		} catch (Exception ex) {
			close();
			ex.printStackTrace();
		}

		return item;
	}

	public String GetLastDocId(String _tblname, String _DocDate)
			throws Exception {
		String retVal = "";
		try {
			openDataBase();
			Cursor c = myDataBase.rawQuery("SELECT Max(DocId) as LastId FROM  "
					+ _tblname + " where DocDate=?", new String[] { _DocDate });
			while (c.moveToNext()) {
				retVal = c.getString(0);
				close();
			}

		} catch (Exception ex) {
			close();
			throw ex;
		}
		return retVal;
	}

	public String[] SaveOrderHeader(String _tblname, A21OrderHeader row)
			throws Exception {
		String[] retVal = new String[] { "0", "" };
		try {
			if (row != null) {
				A21OrderHeader rowUpdate = GetOrderHeaderByIdOrCreatedTime(
						_tblname, String.valueOf(row.Id), true);
				String strLastDocNo = GetLastDocId(_tblname, row.DocDate);
				openDataBase();
				ContentValues valInsert = new ContentValues();
				valInsert.put("Code", row.Code);
				valInsert.put("TrnDate", row.TrnDate);
				valInsert.put("DelDate", row.DelDate);
				valInsert.put("Currency", row.Currency);
				valInsert.put("CustomerPO", row.CustomerPO);
				valInsert.put("Add1", row.Add1);
				valInsert.put("Add2", row.Add2);
				valInsert.put("Add3", row.Add3);
				valInsert.put("Add4", row.Add4);
				valInsert.put("Add5", row.Add5);
				valInsert.put("TrnNo", row.TrnNo);
				valInsert.put("TrnType", row.TrnType);
				valInsert.put("PostedDate", row.PostedDate);
				valInsert.put("SalesPerson", row.SalesPerson);
				valInsert.put("GST", row.GST);
				valInsert.put("Discount", row.Discount);
				valInsert.put("DiscountType", row.DiscountType);
				valInsert.put("GSTInc", row.GSTInc);
				valInsert.put("CreatedDate", row.CreatedDate);
				valInsert.put("CreatedUser", row.CreatedUser);
				valInsert.put("ReservedField1", row.ReservedField1);
				valInsert.put("ReservedField2", row.ReservedField2);
				valInsert.put("ReservedField3", row.ReservedField3);
				valInsert.put("ReservedField4", row.ReservedField4);
				valInsert.put("ReservedField5", row.ReservedField5);
				if (rowUpdate == null) {
					strLastDocNo = strLastDocNo == null ? "" : strLastDocNo;
					int LastDocNo = strLastDocNo.equals("") ? 0 : Integer
							.valueOf(strLastDocNo);
					row.DocId = LastDocNo + 1;
					
					if(Global.getMSTSettings().PrintLayout.equals("Layout1")){
						row.DocNo = Global.getLoggedInUser().DocPrefix + "-"
								+ row.TrnType + row.DocDate + "-" + String.format("%03d", row.DocId); 
					}
					else if(Global.getMSTSettings().PrintLayout.equals("Layout2")){ 
						Date date = new SimpleDateFormat("ddMMyy").parse(row.DocDate) ; 
						SimpleDateFormat dfDoc = new SimpleDateFormat("yyMMdd"); 
						row.DocNo = Global.getLoggedInUser().DocPrefix + "-"
								+ row.TrnType + dfDoc.format(date) + "-" + String.format("%03d", row.DocId); 
					}
					 
					valInsert.put("DocId", row.DocId);
					valInsert.put("DocNo", row.DocNo);
					valInsert.put("DocDate", row.DocDate);
					myDataBase.insert(_tblname, null, valInsert);
					close();
					rowUpdate = GetOrderHeaderByIdOrCreatedTime(_tblname,
							String.valueOf(row.CreatedDate), false);
				} else {
					myDataBase.update(_tblname, valInsert, "Id=?",
							new String[] { String.valueOf(row.Id) });
					close();
				}
				retVal[0] = String.valueOf(rowUpdate.Id);
				retVal[1] = row.DocNo;
			}
		} catch (Exception ex) {
			close();
			throw ex;
		}
		return retVal;
	}

	public void UpdatePostedHeaders(ArrayList<UploadResult> rows) {
		for (UploadResult row : rows) {
			String tableName = "A21OPOrderHeader";
			if (row.TrnType.equals("CW")) {
				tableName = "A21OPOrderHeader";
			} else if (row.TrnType.equals("CA")) {
				tableName = "A21OPCashInvoiceHeader";
			} else if (row.TrnType.equals("CR")) {
				tableName = "A21OPCreditInvoiceHeader";
			}
			A21OrderHeader order = GetOrderHeaderByIdOrCreatedTime(tableName,
					String.valueOf(row.ClientRefID), true);
			if (order != null) {
				openDataBase();
				ContentValues valUpdate = new ContentValues();
				valUpdate.put("TrnNo", row.ServerRefID);
				final SimpleDateFormat df = new SimpleDateFormat(
						"yyyy-MM-dd HH:mm:ss");
				Date currentDate = new Date();
				valUpdate.put("PostedDate", df.format(currentDate));
				myDataBase.update(tableName, valUpdate, "Id=?",
						new String[] { String.valueOf(order.Id) });
				close();
			}
		}
	}

	public A21OrderHeader GetOrderHeaderByIdOrCreatedTime(String _tblname,
			String _Param, boolean IsId) {
		A21OrderHeader item = null;
		try {
			openDataBase();
			Cursor c = myDataBase.rawQuery("Select * from " + _tblname
					+ " where " + (IsId ? "Id=? " : "CreatedDate=? "),
					new String[] { _Param });
			while (c.moveToNext()) {
				item = new A21OrderHeader();
				item.Id = c.getInt(c.getColumnIndex("Id"));
				item.Code = c.getString(c.getColumnIndex("Code"));
				item.TrnDate = c.getString(c.getColumnIndex("TrnDate"));
				item.DelDate = c.getString(c.getColumnIndex("DelDate"));
				item.Currency = c.getString(c.getColumnIndex("Currency"));
				item.CustomerPO = c.getString(c.getColumnIndex("CustomerPO"));
				item.Add1 = c.getString(c.getColumnIndex("Add1"));
				item.Add2 = c.getString(c.getColumnIndex("Add2"));
				item.Add3 = c.getString(c.getColumnIndex("Add3"));
				item.Add4 = c.getString(c.getColumnIndex("Add4"));
				item.TrnNo = c.getString(c.getColumnIndex("TrnNo"));
				item.TrnType = c.getString(c.getColumnIndex("TrnType"));
				item.PostedDate = c.getString(c.getColumnIndex("PostedDate"));
				item.SalesPerson = c.getString(c.getColumnIndex("SalesPerson"));
				item.GST = c.getDouble(c.getColumnIndex("GST"));
				item.Discount = c.getDouble(c.getColumnIndex("Discount"));
				item.GSTInc = c.getInt(c.getColumnIndex("GSTInc"));
				item.CreatedDate = c.getString(c.getColumnIndex("CreatedDate"));
				item.CreatedUser = c.getString(c.getColumnIndex("CreatedUser"));
				item.DocId = c.getInt(c.getColumnIndex("DocId"));
				item.DocNo = c.getString(c.getColumnIndex("DocNo"));
				item.DocDate = c.getString(c.getColumnIndex("DocDate"));
				item.DiscountType = c.getString(c
						.getColumnIndex("DiscountType"));
				item.ReservedField1 = c.getString(c
						.getColumnIndex("ReservedField1"));
				item.ReservedField2 = c.getString(c
						.getColumnIndex("ReservedField2"));
				item.ReservedField3 = c.getString(c
						.getColumnIndex("ReservedField3"));
				item.ReservedField4 = c.getString(c
						.getColumnIndex("ReservedField4"));
				item.ReservedField5 = c.getString(c
						.getColumnIndex("ReservedField5"));
				close();
			}

		} catch (Exception ex) {
			close();
			ex.printStackTrace();
		}

		return item;
	}

	public ArrayList<A21OrderHeader> GetAllOrderHeader(String _tblname) {
		ArrayList<A21OrderHeader> items = new ArrayList<A21OrderHeader>();
		try {
			openDataBase();
			Cursor c = myDataBase.rawQuery("Select " + _tblname
					+ ".*,MSTCustomer.CName from " + _tblname
					+ " inner join MSTCustomer on " + _tblname
					+ ".code=MSTCustomer.code where PostedDate IS NULL", null);
			while (c.moveToNext()) {
				A21OrderHeader item = new A21OrderHeader();
				item.Id = c.getInt(c.getColumnIndex("Id"));
				item.Code = c.getString(c.getColumnIndex("Code"));
				item.TrnDate = c.getString(c.getColumnIndex("TrnDate"));
				item.DelDate = c.getString(c.getColumnIndex("DelDate"));
				item.Currency = c.getString(c.getColumnIndex("Currency"));
				item.CustomerPO = c.getString(c.getColumnIndex("CustomerPO"));
				item.Add1 = c.getString(c.getColumnIndex("Add1"));
				item.Add2 = c.getString(c.getColumnIndex("Add2"));
				item.Add3 = c.getString(c.getColumnIndex("Add3"));
				item.Add4 = c.getString(c.getColumnIndex("Add4"));
				item.TrnNo = c.getString(c.getColumnIndex("TrnNo"));
				item.TrnType = c.getString(c.getColumnIndex("TrnType"));
				item.PostedDate = c.getString(c.getColumnIndex("PostedDate"));
				item.SalesPerson = c.getString(c.getColumnIndex("SalesPerson"));
				item.GST = c.getDouble(c.getColumnIndex("GST"));
				item.Discount = c.getDouble(c.getColumnIndex("Discount"));
				item.GSTInc = c.getInt(c.getColumnIndex("GSTInc"));
				item.CreatedDate = c.getString(c.getColumnIndex("CreatedDate"));
				item.CreatedUser = c.getString(c.getColumnIndex("CreatedUser"));
				item.DocId = c.getInt(c.getColumnIndex("DocId"));
				item.DocNo = c.getString(c.getColumnIndex("DocNo"));
				item.DocDate = c.getString(c.getColumnIndex("DocDate"));
				item.DiscountType = c.getString(c
						.getColumnIndex("DiscountType"));
				item.ReservedField1 = c.getString(c
						.getColumnIndex("ReservedField1"));
				item.ReservedField2 = c.getString(c
						.getColumnIndex("ReservedField2"));
				item.ReservedField3 = c.getString(c
						.getColumnIndex("ReservedField3"));
				item.ReservedField4 = c.getString(c
						.getColumnIndex("ReservedField4"));
				item.ReservedField5 = c.getString(c
						.getColumnIndex("ReservedField5"));
				items.add(item);
				close();
			}

		} catch (Exception ex) {
			close();
			ex.printStackTrace();
		}

		return items;
	}

	public ArrayList<A21OrderHeader> GetOrderSummaryByTable(String tblName) {
		ArrayList<A21OrderHeader> items = new ArrayList<A21OrderHeader>();
		try {
			openDataBase();
			Cursor c = myDataBase.rawQuery("Select " + tblName
					+ ".*,MSTCustomer.CName from " + tblName
					+ " inner join MSTCustomer on " + tblName
					+ ".code=MSTCustomer.code where PostedDate IS NULL ", null);
			while (c.moveToNext()) {
				A21OrderHeader item = new A21OrderHeader();
				item.Id = c.getInt(c.getColumnIndex("Id"));
				item.Code = c.getString(c.getColumnIndex("Code"));
				item.CName = c.getString(c.getColumnIndex("CName"));
				item.TrnDate = c.getString(c.getColumnIndex("TrnDate"));
				item.DelDate = c.getString(c.getColumnIndex("DelDate"));
				item.Currency = c.getString(c.getColumnIndex("Currency"));
				item.CustomerPO = c.getString(c.getColumnIndex("CustomerPO"));
				item.Add1 = c.getString(c.getColumnIndex("Add1"));
				item.Add2 = c.getString(c.getColumnIndex("Add2"));
				item.Add3 = c.getString(c.getColumnIndex("Add3"));
				item.Add4 = c.getString(c.getColumnIndex("Add4"));
				item.TrnNo = c.getString(c.getColumnIndex("TrnNo"));
				item.TrnType = c.getString(c.getColumnIndex("TrnType"));
				item.PostedDate = c.getString(c.getColumnIndex("PostedDate"));
				item.DiscountType = c.getString(c
						.getColumnIndex("DiscountType"));
				item.DocId = c.getInt(c.getColumnIndex("DocId"));
				item.DocNo = c.getString(c.getColumnIndex("DocNo"));
				item.DocDate = c.getString(c.getColumnIndex("DocDate"));

				item.ReservedField1 = c.getString(c
						.getColumnIndex("ReservedField1"));
				item.ReservedField2 = c.getString(c
						.getColumnIndex("ReservedField2"));
				item.ReservedField3 = c.getString(c
						.getColumnIndex("ReservedField3"));
				item.ReservedField4 = c.getString(c
						.getColumnIndex("ReservedField4"));
				item.ReservedField5 = c.getString(c
						.getColumnIndex("ReservedField5"));
				items.add(item);
				close();
			}

		} catch (Exception ex) {
			close();
			ex.printStackTrace();
		}

		return items;
	}

	public ArrayList<A21OrderHeader> GetOrderSummaryByTableCustomer(
			String tblName, String _Customer) {
		ArrayList<A21OrderHeader> items = new ArrayList<A21OrderHeader>();
		try {
			openDataBase();
			Cursor c = myDataBase.rawQuery("Select " + tblName
					+ ".*,MSTCustomer.CName from " + tblName
					+ " inner join MSTCustomer on " + tblName
					+ ".code=MSTCustomer.code where PostedDate IS NULL and "
					+ tblName + ".Code=? ", new String[] { _Customer });
			while (c.moveToNext()) {
				A21OrderHeader item = new A21OrderHeader();
				item.Id = c.getInt(c.getColumnIndex("Id"));
				item.Code = c.getString(c.getColumnIndex("Code"));
				item.CName = c.getString(c.getColumnIndex("CName"));
				item.TrnDate = c.getString(c.getColumnIndex("TrnDate"));
				item.DelDate = c.getString(c.getColumnIndex("DelDate"));
				item.Currency = c.getString(c.getColumnIndex("Currency"));
				item.CustomerPO = c.getString(c.getColumnIndex("CustomerPO"));
				item.Add1 = c.getString(c.getColumnIndex("Add1"));
				item.Add2 = c.getString(c.getColumnIndex("Add2"));
				item.Add3 = c.getString(c.getColumnIndex("Add3"));
				item.Add4 = c.getString(c.getColumnIndex("Add4"));
				item.TrnNo = c.getString(c.getColumnIndex("TrnNo"));
				item.TrnType = c.getString(c.getColumnIndex("TrnType"));
				item.PostedDate = c.getString(c.getColumnIndex("PostedDate"));
				item.DiscountType = c.getString(c
						.getColumnIndex("DiscountType"));
				item.DocId = c.getInt(c.getColumnIndex("DocId"));
				item.DocNo = c.getString(c.getColumnIndex("DocNo"));
				item.DocDate = c.getString(c.getColumnIndex("DocDate"));

				item.ReservedField1 = c.getString(c
						.getColumnIndex("ReservedField1"));
				item.ReservedField2 = c.getString(c
						.getColumnIndex("ReservedField2"));
				item.ReservedField3 = c.getString(c
						.getColumnIndex("ReservedField3"));
				item.ReservedField4 = c.getString(c
						.getColumnIndex("ReservedField4"));
				item.ReservedField5 = c.getString(c
						.getColumnIndex("ReservedField5"));
				items.add(item);
				close();
			}

		} catch (Exception ex) {
			close();
			ex.printStackTrace();
		}

		return items;
	}

	public ArrayList<A21OrderHeader> GetAllOrderSummary() {
		ArrayList<A21OrderHeader> items = new ArrayList<A21OrderHeader>();
		try {
			openDataBase();
			String qry = "Select A21OPOrderHeader.*,MSTCustomer.CName as CName from A21OPOrderHeader inner join MSTCustomer on A21OPOrderHeader.code=MSTCustomer.code where PostedDate IS NULL "
					+ "UNION ALL "
					+ "Select A21OPCashInvoiceHeader.*,MSTCustomer.CName as CName from A21OPCashInvoiceHeader inner join MSTCustomer on A21OPCashInvoiceHeader.code=MSTCustomer.code where PostedDate IS NULL "
					+ "UNION ALL "
					+ "Select A21OPCreditInvoiceHeader.*,MSTCustomer.CName as CName from A21OPCreditInvoiceHeader inner join MSTCustomer on A21OPCreditInvoiceHeader.code=MSTCustomer.code where PostedDate IS NULL "
					+ "ORDER BY TrnDate Desc ";
			Cursor c = myDataBase.rawQuery(qry, null);
			while (c.moveToNext()) {
				A21OrderHeader item = new A21OrderHeader();
				item.Id = c.getInt(c.getColumnIndex("Id"));
				item.Code = c.getString(c.getColumnIndex("Code"));
				item.CName = c.getString(c.getColumnIndex("CName"));
				item.TrnDate = c.getString(c.getColumnIndex("TrnDate"));
				item.DelDate = c.getString(c.getColumnIndex("DelDate"));
				item.Currency = c.getString(c.getColumnIndex("Currency"));
				item.CustomerPO = c.getString(c.getColumnIndex("CustomerPO"));
				item.Add1 = c.getString(c.getColumnIndex("Add1"));
				item.Add2 = c.getString(c.getColumnIndex("Add2"));
				item.Add3 = c.getString(c.getColumnIndex("Add3"));
				item.Add4 = c.getString(c.getColumnIndex("Add4"));
				item.TrnNo = c.getString(c.getColumnIndex("TrnNo"));
				item.TrnType = c.getString(c.getColumnIndex("TrnType"));
				item.PostedDate = c.getString(c.getColumnIndex("PostedDate"));
				item.DiscountType = c.getString(c
						.getColumnIndex("DiscountType"));
				item.DocId = c.getInt(c.getColumnIndex("DocId"));
				item.DocNo = c.getString(c.getColumnIndex("DocNo"));
				item.DocDate = c.getString(c.getColumnIndex("DocDate"));

				item.ReservedField1 = c.getString(c
						.getColumnIndex("ReservedField1"));
				item.ReservedField2 = c.getString(c
						.getColumnIndex("ReservedField2"));
				item.ReservedField3 = c.getString(c
						.getColumnIndex("ReservedField3"));
				item.ReservedField4 = c.getString(c
						.getColumnIndex("ReservedField4"));
				item.ReservedField5 = c.getString(c
						.getColumnIndex("ReservedField5"));
				items.add(item);
				close();
			}

		} catch (Exception ex) {
			close();
			ex.printStackTrace();
		}

		return items;
	}

	public int SaveOrderDetail(String _tblname, TrnDetail row) throws Exception {
		int retVal = 0;
		try {
			if (row != null) {
				TrnDetail rowUpdate = GetOrderDetailById(_tblname,
						String.valueOf(row.Id));
				openDataBase();
				ContentValues valInsert = new ContentValues();
				valInsert.put("HeaderId", row.HeaderId);
				valInsert.put("Product", row.Product);
				valInsert.put("ProdDesc", row.ProdDesc);
				valInsert.put("OrderQuantity", row.OrderQuantity);
				valInsert.put("Conversion", row.Conversion);
				valInsert.put("UnitPrice", row.UnitPrice);
				valInsert.put("LineDiscount1", row.LineDiscount1);
				valInsert.put("UOM", row.UOM);
				valInsert.put("StockUnit", row.StockUnit);
				valInsert.put("DetailMemo", row.DetailMemo);
				valInsert.put("ReservedField1", row.ReservedField1);
				valInsert.put("ReservedField2", row.ReservedField2);
				valInsert.put("ReservedField3", row.ReservedField3);
				valInsert.put("ReservedField4", row.ReservedField4);
				valInsert.put("ReservedField5", row.ReservedField5);
				if (rowUpdate == null) {
					myDataBase.insert(_tblname, null, valInsert);
				} else {
					myDataBase.update(_tblname, valInsert, "Id=?",
							new String[] { String.valueOf(row.Id) });
				}
				close();
				if (rowUpdate == null) {
					retVal = GetLastInsertedDetailId(_tblname);
				} else {
					retVal = rowUpdate.Id;
				}
			}
		} catch (Exception ex) {
			close();
			throw ex;
		}
		return retVal;
	}

	public int GetLastInsertedDetailId(String tblname) {
		int lastId = 0;
		openDataBase();
		Cursor c = myDataBase.rawQuery("SELECT max(Id) from " + tblname, null);
		while (c.moveToNext()) {
			lastId = c.getInt(0);
		}
		close();
		return lastId;
	}

	public TrnDetail GetOrderDetailById(String _tblname, String _Id) {
		TrnDetail item = null;
		try {
			openDataBase();
			Cursor c = myDataBase.rawQuery("Select * from " + _tblname
					+ " where Id=?", new String[] { _Id });
			while (c.moveToNext()) {
				item = new TrnDetail();
				item.Id = c.getInt(c.getColumnIndex("Id"));
				item.HeaderId = c.getInt(c.getColumnIndex("HeaderId"));
				item.Product = c.getString(c.getColumnIndex("Product"));
				item.ProdDesc = c.getString(c.getColumnIndex("ProdDesc"));
				item.OrderQuantity = c.getDouble(c
						.getColumnIndex("OrderQuantity"));
				item.Conversion = c.getDouble(c.getColumnIndex("Conversion"));
				item.UnitPrice = c.getDouble(c.getColumnIndex("UnitPrice"));
				item.LineDiscount1 = c.getDouble(c
						.getColumnIndex("LineDiscount1"));
				item.UOM = c.getString(c.getColumnIndex("UOM"));
				item.StockUnit = c.getString(c.getColumnIndex("StockUnit"));
				item.DetailMemo = c.getString(c.getColumnIndex("DetailMemo"));
				item.ReservedField1 = c.getString(c
						.getColumnIndex("ReservedField1"));
				item.ReservedField2 = c.getString(c
						.getColumnIndex("ReservedField2"));
				item.ReservedField3 = c.getString(c
						.getColumnIndex("ReservedField3"));
				item.ReservedField4 = c.getString(c
						.getColumnIndex("ReservedField4"));
				item.ReservedField5 = c.getString(c
						.getColumnIndex("ReservedField5"));
				close();
			}

		} catch (Exception ex) {
			close();
			ex.printStackTrace();
		}

		return item;
	}

	public ArrayList<TrnDetail> GetOrderDetailByHeaderId(String _tblname,
			String _HeaderId) {
		ArrayList<TrnDetail> items = new ArrayList<TrnDetail>();
		try {
			openDataBase();
			Cursor c = myDataBase.rawQuery("Select " + _tblname
					+ ".*,MSTProduct.PName from " + _tblname
					+ " Left join MSTProduct on " + _tblname
					+ ".Product=MSTProduct.Product where HeaderId=?",
					new String[] { _HeaderId });
			while (c.moveToNext()) {
				TrnDetail item = new TrnDetail();
				item.Id = c.getInt(c.getColumnIndex("Id"));
				item.HeaderId = c.getInt(c.getColumnIndex("HeaderId"));
				item.Product = c.getString(c.getColumnIndex("Product"));
				item.PName = c.getString(c.getColumnIndex("PName"));
				item.ProdDesc = c.getString(c.getColumnIndex("ProdDesc"));
				item.OrderQuantity = c.getDouble(c
						.getColumnIndex("OrderQuantity"));
				item.Conversion = c.getDouble(c.getColumnIndex("Conversion"));
				item.UnitPrice = c.getDouble(c.getColumnIndex("UnitPrice"));
				item.LineDiscount1 = c.getDouble(c
						.getColumnIndex("LineDiscount1"));
				item.UOM = c.getString(c.getColumnIndex("UOM"));
				item.StockUnit = c.getString(c.getColumnIndex("StockUnit"));
				item.DetailMemo = c.getString(c.getColumnIndex("DetailMemo"));
				item.ReservedField1 = c.getString(c
						.getColumnIndex("ReservedField1"));
				item.ReservedField2 = c.getString(c
						.getColumnIndex("ReservedField2"));
				item.ReservedField3 = c.getString(c
						.getColumnIndex("ReservedField3"));
				item.ReservedField4 = c.getString(c
						.getColumnIndex("ReservedField4"));
				item.ReservedField5 = c.getString(c
						.getColumnIndex("ReservedField5"));
				items.add(item);
				close();
			}

		} catch (Exception ex) {
			close();
			ex.printStackTrace();
		}

		return items;
	}

	public void DeleteRowByTableId(String _tblname, String _Id) {
		try {
			openDataBase();
			myDataBase.delete(_tblname, "Id=?", new String[] { _Id });
			if (_tblname.equals("A21OPOrderHeader")) {
				myDataBase.delete("A21OPOrderDetail", "HeaderId=?",
						new String[] { _Id });
			} else if (_tblname.equals("A21OPCashInvoiceHeader")) {
				myDataBase.delete("A21OPCashInvoiceDetail", "HeaderId=?",
						new String[] { _Id });
			} else if (_tblname.equals("A21OPCreditInvoiceHeader")) {
				myDataBase.delete("A21OPCreditInvoiceDetail", "HeaderId=?",
						new String[] { _Id });
			}
			close();
		} catch (Exception ex) {
			close();
			ex.printStackTrace();
		}
	}

	// Get Customer info from CustomerInfo tbl.
	public ArrayList<String> GetCustomerInfo(String _CustomerCode) {
		ArrayList<String> AryCustoemrInfo = new ArrayList<String>();
		try {
			openDataBase();

			Cursor c = myDataBase
					.rawQuery(
							"Select Add1,Add2,Add2,Add3,Add4  from MSTCustomer  where Code=? ",
							new String[] { _CustomerCode });

			while (c.moveToNext()) {
				AryCustoemrInfo.add(c.getString(0));// Address1
				AryCustoemrInfo.add(c.getString(1));// Address2
				AryCustoemrInfo.add(c.getString(2));// Address3
				AryCustoemrInfo.add(c.getString(3));// Address4
				AryCustoemrInfo.add(c.getString(4));// Address5
			}

			close();

		} catch (Exception ex) {
			close();
			ex.printStackTrace();
		}
		return AryCustoemrInfo;
	}

	
	public SpinnerObject[] GetAllModel() {

		SpinnerObject[] arrlst=null;
		try {
			openDataBase();
			Cursor c = myDataBase.rawQuery( "Select Code,Description from SUProductModel order by Description", null);
			arrlst = new SpinnerObject[c.getCount() + 1];
			SpinnerObject row=new SpinnerObject("None","None"); ;
			arrlst[0] =row;  
			int i = 0 + 1;
			while (c.moveToNext()) {
			    String Code=c.getString(0);
			    String Description=c.getString(1);
			    row=new SpinnerObject(Code,Description); 
				arrlst[i] =row; 
				i++;
			}

		} catch (Exception e) {
			int k = 0;
		}
		myDataBase.close();
		return arrlst;
	}
	
	
	public SpinnerObject[] GetAllMade() {

		SpinnerObject[] arrlst=null;
		try {
			openDataBase();
			Cursor c = myDataBase.rawQuery( "Select Code,Description from SUProductMade order by Description", null);
			arrlst = new SpinnerObject[c.getCount() + 1];
			SpinnerObject row=new SpinnerObject("None","None"); ;
			arrlst[0] =row;  
			int i = 0 + 1;
			while (c.moveToNext()) {
			    String Code=c.getString(0);
			    String Description=c.getString(1);
			    row=new SpinnerObject(Code,Description); 
				arrlst[i] =row; 
				i++;
			}

		} catch (Exception e) {
			int k = 0;
		}
		myDataBase.close();
		return arrlst;
	}
	
	
	public SpinnerObject[] GetAllBrand() {

		SpinnerObject[] arrlst=null;
		try {
			openDataBase();
			Cursor c = myDataBase.rawQuery( "Select Code,Description from SUProductBrand order by Description", null);
			arrlst = new SpinnerObject[c.getCount() + 1];
			SpinnerObject row=new SpinnerObject("None","None"); ;
			arrlst[0] =row;  
			int i = 0 + 1;
			while (c.moveToNext()) {
			    String Code=c.getString(0);
			    String Description=c.getString(1);
			    row=new SpinnerObject(Code,Description); 
				arrlst[i] =row; 
				i++;
			}

		} catch (Exception e) {
			int k = 0;
		}
		myDataBase.close();
		return arrlst;
	}
	
	
	public SpinnerObject[] GetCustomerLocations(String _CustomerCode) {

		SpinnerObject[] arrlst=null;
		try {
			openDataBase();
			Cursor c = myDataBase.rawQuery( "Select Location,Description from MSTCustomerLocation Where Code=? ",
							new String[] { _CustomerCode });
			arrlst = new SpinnerObject[c.getCount() + 2];
			SpinnerObject row=new SpinnerObject("None","None"); ;
			arrlst[0] =row; 
			row=new SpinnerObject("Billing Address","Billing Address"); 
			arrlst[1] =row;   
			int i = 0 + 2;
			while (c.moveToNext()) {
			    String Location=c.getString(0);
			    String Description=c.getString(1);
			    row=new SpinnerObject(Location,Description); 
				arrlst[i] =row; 
				i++;
			}

		} catch (Exception e) {
			int k = 0;
		}
		myDataBase.close();
		return arrlst;
	}
	
	// Get Customer Location Details from Customer location Details tbl.
	/*public ArrayList<String> GetCustomerLocations(String _CustomerCode) {
		ArrayList<String> AryCustomerLocation = new ArrayList<String>();
		try {
			openDataBase();

			Cursor c = myDataBase.rawQuery(
					"Select Location from MSTCustomerLocation  where Code=?",
					new String[] { _CustomerCode });
			while (c.moveToNext()) {
				AryCustomerLocation.add(c.getString(0));
			}

			close();
		} catch (Exception ex) {
			close();

			ex.printStackTrace();
		}
		return AryCustomerLocation;
	}*/

	// Get Customer Location Details from Customer location Details tbl.
	public ArrayList<String> GetCustomerLocationDetails(String _LocationID,
			String _CustomerCode) {
		ArrayList<String> AryCustomerLocationDetails = new ArrayList<String>();
		try {
			openDataBase();

			Cursor c = myDataBase
					.rawQuery(
							"Select Add1,Add2,Add3,Add4,Add5 from MSTCustomerLocation where Location=? and Code=?",
							new String[] { _LocationID, _CustomerCode });
			while (c.moveToNext()) {
				AryCustomerLocationDetails.add(c.getString(0));
				AryCustomerLocationDetails.add(c.getString(1));
				AryCustomerLocationDetails.add(c.getString(2));
				AryCustomerLocationDetails.add(c.getString(3));
				AryCustomerLocationDetails.add(c.getString(4));
			}

			close();
		} catch (Exception ex) {
			close();
			ex.printStackTrace();
		}
		return AryCustomerLocationDetails;
	}

	/**
	 * Creates a empty database on the system and rewrites it with your own
	 * database.
	 * */
	public void createDataBase() throws IOException {
		boolean dbExist = checkDataBase();
		if (dbExist) {
			int CurrentVersion = getVersion();
			/*if (CurrentVersion != DATABASE_VERSION) {
				if (CurrentVersion < 2) {
					UpdateVersion2();
				}

			}*/
		} else {
			this.getReadableDatabase();
			try {
				copyDataBase();

			} catch (IOException e) {
				throw new Error("Error copying database");
			}
		}

	}

	private void UpdateVersion2() {
		openDataBase();
		String Sql = "CREATE TABLE 'MSTMultiTierDiscountOP' ("
				+ "'ID'	INTEGER," + "'Code'	TEXT," + "'Product'	TEXT,"
				+ "'Currency'	TEXT," + "'UOM'	TEXT,"
				+ "'L1NormalPrice'	NUMERIC," + "'L1TDiscAmt'	NUMERIC,"
				+ "'L1TDiscPercent'	NUMERIC," + "'L1DateFrom'	TEXT,"
				+ "'L1DateTo'	TEXT," + "'L2DiscAmt'	NUMERIC,"
				+ "'L2DiscPercent'	NUMERIC," + "'L2TDiscAmt'	NUMERIC,"
				+ "'L2TDiscPercent'	NUMERIC," + "'L2DateFrom'	TEXT,"
				+ "'L2DateTo'	TEXT," + "'L3MarkDownPrice'	NUMERIC,"
				+ "'L3TDiscAmt'	NUMERIC," + "'L3TDiscPercent'	NUMERIC,"
				+ "'L3DateFrom'	TEXT," + "'L3DateTo'	TEXT," + "PRIMARY KEY(ID)"
				+ ") ";
		myDataBase.execSQL(Sql);
		myDataBase
				.execSQL("ALTER TABLE MSTSettings ADD COLUMN DiscountLock TEXT");
		myDataBase.execSQL("ALTER TABLE MSTSettings ADD COLUMN PriceLock TEXT");
		myDataBase.execSQL("ALTER TABLE MSTProduct ADD COLUMN Model TEXT");

		Sql = "CREATE TABLE 'SUProductMade' ( " + " 'Code'	TEXT, "
				+ " 'Description'	TEXT " + ")";
		myDataBase.execSQL(Sql);

		Sql = "CREATE TABLE 'SUProductModel' ( " + " 'Code'	TEXT, "
				+ " 'Description'	TEXT " + ")";
		myDataBase.execSQL(Sql);
		
		Sql = "CREATE TABLE 'SUProductBrand' ( " + " 'Code'	TEXT, "
				+ " 'Description'	TEXT " + ")";
		myDataBase.execSQL(Sql);
		
		ContentValues valUpdate = new ContentValues();
		valUpdate.put("Version", 2);
		myDataBase.update("DBVersion", valUpdate, "Version=?",
				new String[] { String.valueOf("1") });
		close();
	}

	public int getVersion() {
		openDataBase();
		int Version = 1;
		Cursor c = myDataBase.rawQuery("Select * from DBVersion", null);
		while (c.moveToNext()) {
			Version = Integer.valueOf(c.getString(c.getColumnIndex("Version")));
		}
		close();
		return Version;
	}

	/**
	 * Check if the database already exist to avoid re-copying the file each
	 * time you open the application.
	 * 
	 * @return true if it exists, false if it doesn't
	 */
	private boolean checkDataBase() {

		SQLiteDatabase checkDB = null;

		try {
			String myPath = DB_PATH + DB_NAME;
			checkDB = SQLiteDatabase.openDatabase(myPath, null,
					SQLiteDatabase.OPEN_READONLY);

		} catch (SQLiteException e) {

			// database does't exist yet.

		}

		if (checkDB != null) {

			checkDB.close();

		}

		return checkDB != null ? true : false;
	}

	/**
	 * Copies your database from your local assets-folder to the just created
	 * empty database in the system folder, from where it can be accessed and
	 * handled. This is done by transfering bytestream.
	 * */
	private void copyDataBase() throws IOException {

		// Open your local db as the input stream
		InputStream myInput = myContext.getAssets().open(DB_NAME);
		// Path to the just created empty db
		String outFileName = DB_PATH + DB_NAME;
		// Open the empty db as the output stream
		OutputStream myOutput = new FileOutputStream(outFileName);
		// transfer bytes from the inputfile to the outputfile
		byte[] buffer = new byte[1024];
		int length;
		while ((length = myInput.read(buffer)) > 0) {
			myOutput.write(buffer, 0, length);
		}
		// Close the streams
		myOutput.flush();
		myOutput.close();
		myInput.close();

	}

	public void openDataBase() throws SQLException {

		// Open the database
		String myPath = DB_PATH + DB_NAME;
		myDataBase = SQLiteDatabase.openDatabase(myPath, null,
				SQLiteDatabase.OPEN_READWRITE);

	}

	@Override
	public synchronized void close() {

		if (myDataBase != null)
			myDataBase.close();

		super.close();
	}

	@Override
	public void onCreate(SQLiteDatabase db) {

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

	}

}